Dates and Times

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);

Could you convert the dates into epoch seconds then do a subtraction? For example:

(datetime_to_epoch(date1) - datetime_to_epoch(date2) )/86400


Hi Xinyu - I still dont think that works because I would get the time within each day. They key is to first isolate the date by itself without any time.

OK. To truncate to days, divide by 86400 first. As it is an integer divide, the hours/seconds etc are removed.:

// 86400 = 60seconds60minutes24hours = seconds in a day

(datetime_to_epoch(date1)/86400) - (datetime_to_epoch(date2)/86400)

This will give you the number of days difference.


Richard Henderson.