filter on specific date without caring about min/sec

[dijam]

what is a good way to filter on specific date?
I am storing data as full date format with timezone like 2018-08-06T14:21:54+02:00 and I am trying to query for specific day like: 2018-08-06
There are only few functionalities available for Date field and none support something like includes.
Any suggestion?

ps: I know one solution would be making a function/store procedure in Postgres and then call it in the graphql but that is quite limited.

[mattbretl]

You're right. Currently you'd have to write something like:

query {
  allFoos (filter: {
    bar: {
      greaterThanOrEqualTo: "2018-08-06",
      lessThan: "2018-08-07"
    }
  }) {
    nodes {
      bar
    }
  }
}

I can think of a few approaches:

1. Expose the existing like operator, which would translate { bar: { like: "2018-08-06%" } } to a where clause of bar::text LIKE '2018-08-06%'. This would be also support queries on year, year-month, year-month-day-hour, etc.

2. Create a new dateEqualTo operator, which would translate { bar: { dateEqualTo: "2018-08-06" } } to a where clause of bar::date = '2018-08-06'. This would likely be more performant, especially with an expression index.

@benjie, any thoughts?

[mattbretl]

Also, we may need to consider time zones.. 2018-08-06T14:21:54+02:00 will resolve to a different short date string depending on the time zone. Need to give this some more thought.

[mattbretl]

Thoughts on time zones..

The approaches described above would allow you to filter based on the time zone setting of the database (run show timezone; to check). So if your database is set to 'Europe/Stockholm', then you could filter for rows where the date portion of the 'Europe/Stockholm'-formatted timestamp is equal to 2018-08-06.

But if the database time zone is set to UTC, and you want to match on 2018-08-06 in a 'Europe/Stockholm' context, that'll be trickier. Some options:

1. Run SET LOCAL TIME ZONE 'Europe/Stockholm'; at the start of the transaction (though this will affect the all date fields returned from the query, which could impact how you process dates client-side).
2. Include AT TIME ZONE in the where clause, such as: (bar at time zone 'Europe/Stockholm')::date = '2018-08-06'.

[benjie]

Personally I’d stick with the “greaterThanOrEqualTo” / “lessThan” combo - it’s more flexible and more explicit. All my databases use UTC. I’m not comfortable exposing a “like” operator - serious performance/DOS issues there; beginsWith would be better.

[mattbretl]

Yeah, taking a fresh look at this, it really is implementation-specific and shouldn't be exposed by default.

For anyone that comes across this in the future, here's an example plugin that you can append after postgraphile-plugin-connection-filter to enable a datetimeStartsWith operator (but as Benjie mentioned, you should definitely consider the performance/DOS concerns):

module.exports = function PgConnectionArgFilterDatetimeOperatorsPlugin(builder) {
  builder.hook("init", (_, build) => {
    const {
      addConnectionFilterOperator,
      escapeLikeWildcards,
      pgSql: sql,
    } = build;
    addConnectionFilterOperator(
      "datetimeStartsWith",
      "Checks for date strings starting with this value.  Case sensitive.",
      fieldType => fieldType,
      (identifier, val) => {
        return sql.query`${identifier}::text LIKE ${val}`;
      },
      {
        allowedFieldTypes: ["Datetime"],
        inputResolver: input => `${escapeLikeWildcards(input)}%`,
      }
    );
    return _;
  });
};