Dates and Times

GSQL
#1

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

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

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

Thanks.

#3

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.

#4

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.

Regards

Richard Henderson.