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.

[treyhuffine]

Is there any movement on this? This would be an awesome addition!

[fishactual]

Would love to see this land at some point. Any updates?

[GameScripting]

As suggested per https://github.com/hasura/graphql-engine/issues/1920, we implemented this using a SQL view. For more dynamic use cases maybe create a Computed Field and filter on that.

[nbouvrette]

If I am not mistaken, if you have a expires column, and you want to set it to now() + interval '1' day this is not possible today with Hasura.

Meaning that you have to rely on either a server time or even worse, client time to estimate the value in the expires fields... which of course could be different than the SQL server.

This could lead to problematic side effects and having this supported directly in Hasura would be really helpful.

In the meantime, I'll see if it's possible to either use queries to get the server time or as a last resort a trigger to update the time.. which is of course not ideal.

Edit:

It looks like it's not possible to use queries to get the server time, as custom functions need to map to a table (at least from what I could tell). I also found a better way than using a trigger, by leveraging the default values like this:

(now() + '1 day'::interval)

And then you can make queries like expires_at > now()

[xardit]

+1

[SamirTalwar]

Hey folks. As a little sneak preview: Native Queries will be out of beta and available in Hasura CE for PostgreSQL with the release of v2.28, which is imminent. Native Queries allow you to express your query as arbitrary SQL, so you can write more complex filters such as this one, and still benefit from further filtering, sorting, etc. when writing your GraphQL query.

Please check them out!

[100tomer]

+1

[joanrodriguez]

+1