Graphql-engine: Support for semantic date queries

[cthurston]

The idea is that we add support for queries that look like this:

where: { myDateTime: { _semantic: { value: "TODAY", timezone: "+6:00" } } }

Timezone could default to server time.
{ myDateTime: { _semantic: { value: "NEXT_N_DAYS", n: 22 } } }

{ myDateTime: { _semantic: { value: "PAST_N_WEEKS", timezone: "+6:00", n: 3 } } }

{ myDateTime: { _semantic: { value: "LAST_MONTH", timezone: "+6:00" } } }

{ myDateTime: { _semantic: { value: "NEXT_N_HOURS", timezone: "+6:00", n: 3 } } }

These are inspired by the Salesforce API which calls them literals. See: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

The SQL can be implemented something like this:

Yesterday for example:
( "myDateTimeField" >= (date_trunc('DAY', now() AT TIME ZONE '+6:00') - '1 DAY'::INTERVAL) AT TIME ZONE '+6:00' AND "myDateTimeField" < (date_trunc('DAY', now() AT TIME ZONE '+6:00')) AT TIME ZONE '+6:00' )
Or parameterized:
pg.query( "?? >= (date_trunc('DAY', now() AT TIME ZONE ?) - '1 DAY'::INTERVAL) AT TIME ZONE ? AND ?? < (date_trunc('DAY', now() AT TIME ZONE ?)) AT TIME ZONE ?", [field, zone, zone, field, zone, zone] )

I can collaborate to produce the SQL required for the other semantic dates that we want to support if this feature request has merit.

[0x777]

These would be great additions. Thanks for putting in the time to write this. The syntax looks neat but I see a couple of issues:

_semantic: {
  value: "PAST_N_WEEKS",
  timezone: "+6:00",
  n: 3
}

1. It would be very hard to document the n field, as it is dependent (i.e, n could be days, months, null et) on the value of value field.
2. The fields timezone and n have to be nullable (have to be to support an arbitrary value) and hence incorrect use of the API is not captured with types (and hence in validation phase). For people who like code-gen tools this may not be acceptable.

It'll not be hard to add this once we agree on the syntax.

[cthurston]

Salesforce does it this way:
"PAST_N_WEEKS:3"
Which I didn't like as much as having n outside the string because the client would have to build the string rather than just pass n as a variable.

I suppose you could put the timezone in as well:
"PAST_N_WEEKS:3:+6:00"
But now the timezone has a colon so you may want a different delimiter.
"PAST_N_WEEKS,3,+6:00"

Looks impossible to validate.
_semantic: "PAST_N_WEEKS,3,+6:00"

OR

We could make them all keys:
_semantic: { _yesterday: true } _semantic: { _yesterday: 1 }
_semantic: { _past_n_weeks: 3 _timezone: "+6:00" }
or some combination with some string parsing
_semantic: { _yesterday: "+6:00" }
_semantic: { _past_n_weeks: "3,+6:00" }

OR

Nesting with a required interval-type object

_semantic: { _yesterday: { _timezone: "+6:00" _value: 1 } }
defaults to server timezone and _value of 1 maybe
_semantic: { _yesterday: {} }
_semantic: { _past_n_weeks: { _timezone: "+6:00" _value: 3 } }

The nested syntax would look great in graphiQL.

With the keys they could even be taken out of the _semantic namespace and be top level, but maybe too messy to have _gte and _yesterday on the same tier.

[tsaiDavid]

Can't wait for this!

@dmi3y

[ptrobert]

1) For Time related npm packages--->moment and moment-timezone package functions will be the most sort by the users e.g startofday, endofday for a given timezone etc,
2) As Postgres cant save timezones mostly users save that in a separate column for reference purpose, so in the proposal along with providing timezone as param, a timezone column should also be allowed.