Fun with Dates and Times

[Mark Megerian]

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

[Xinyu Chang]

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

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

Thanks.

Xinyu Chang

Director of Customer Solutions

TigerGraph, Inc.

xi...@tigergraph.com

--
Welcome to GSQL-user group.
- our mission is bringing the power of graph databases to everyone www.opengsql.org
- technical resource can be found here https://docs.tigergraph.com/
---
You received this message because you are subscribed to the Google Groups "gsql-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gsql-users+...@opengsql.org.
To view this discussion on the web visit https://groups.google.com/a/opengsql.org/d/msgid/gsql-users/557d62e0-0199-4f55-8921-e9af58507ee9%40opengsql.org.

[Mark Megerian]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to gsql-...@opengsql.org.

[Rik]

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

// 86400 = 60seconds*60minutes*24hours = 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.