DateTime Functions are needed

Continuing the discussion from Filter Datetime on month or day / advanced Datetime functions:

I guess I am just now realizing that this is not here anywhere (at least that I can find).

Every Database that I have ever worked with before has had Date/Time functions built in. I have the prerequisite of not storing different parts of a DateTime into separate searchable fields. I need to be able to do filtering based upon DateTime that matches a particular month, or a particular day, or a particular hour.

Use Case 1: Filter a list of Contact’s Birthdays that is today. If it is saved as a DateTime scalar with the accurate birth year as is needed for calculating age, then I cannot filter by Date unless I also specify a year.

Use Case 2: Filter a list of Activities that occurred in a particular month, or on a particular date, or on a particular day of week. Building our application, we want to be able to show users when their friends are most active based upon these times. This can be used for schedule matching. If who I am trying to reach is usually active during this time period on these particular days of the week then that is when I will try to reach out to them.

Use Case 3: Filter a list of Events that occurred in a particular month. I want to be able to show what events happened during this month at this location in the years past. Users want to see if maybe there is another similar event that might be going on but is not documented yet. Used for looking forward and planning based on normal activities during a month.

I have more use cases if you need more. Can this be done now with current functions/var blocks?

I am thinking that maybe I could do some math based off from the epoch times to find dates in a specific year/month/hour/DoM/DoY/etc.

Related Posts:

2 Likes

Well, this didn’t get much traction :frowning:

Here is what I have so far to use in a DQL var block to get a few of the pieces that I will need.

Day of Week:

floor(((dateVal+(5*86400)) % 604800)/86400)

Hour of Day:

floor((dateVal % 86400)/3600)

~ Week of Year: (still not exact for some dates)

ceil((((dateVal-(2*31536000)) % 126230400)-((((floor(dateVal/31557600)+1970) & 4))*31536000))/604800)

~ Day of Year: (still has a bug whree the day is 1± from actual)

floor((((dateVal-(2*31536000)) % 126230400)-((((floor(dateVal/31557600)+1970) % 4))*31536000))/86400)

A little bit tricker to figure out is the DateOfMonth and MonthOfYear since leap year and our abnormal calendar really throws a wrench into this mathematically. Seeing that math supports cond it might be possible still with a few layers of conditions. Probably not best though.

@core-devs thoughts on using math like this in a var block to get the DateTime functionality needed?

4 Likes

Sorry, I recategorized this under Issues now. It should see some traction from devs

1 Like

The logic seems complex and more error-prone. As an improvement, we can share the responsibility between query and server/application itself. For example, consider we need to find events in a particular month of a year say Aug 2020.

From the given timestamp say 1598894650 (in seconds), we can find the number of days elapsed from 1/1/1970 via 1598894650/86400 = 18505. Also, the application can easily find the days range for 1-30 Aug 2020 from 1/1/1970 which will be [18,475, 18506]. Now it just becomes a simple range query. This logic will have better error handling and more predictable results.

In the meantime, I’ll check the current status of this request.

2 Likes

“Simple Range Query” is not possible with GraphQL right now though either… see:

@hardik is this on our roadmap somewhere?

1 Like