I needed to get differences in days, between dates. After reading all the options for DATETIME arithmetic, I couldn’t find anything to easily do this, so I wrote the following logic.
It works perfectly, but is a bit ugly. Is there a better way? If not, at least I am sharing an example for others to use:
DATETIME rightNow;
rightNow = now();
rightNow = datetime_sub(rightNow, INTERVAL hour(now()) HOUR);
rightNow = datetime_sub(rightNow, INTERVAL minute(now()) MINUTE);
rightNow = datetime_sub(rightNow, INTERVAL second(now()) SECOND);
…
H3 = SELECT h FROM H3:h
ACCUM @@dates += (datetime_diff(datetime_sub(datetime_sub(datetime_sub(h.actualDate, INTERVAL hour(h.actualDate) HOUR), INTERVAL minute(h.actualDate) MINUTE), INTERVAL second(h.actualAdmissionDate) SECOND), rightNow)/86400 -> 1);