Hasura Query: Select all entries, which are between two specific dates and in a specific time range

Question

I need to query data from a table in Hasura. The structure is mostly irrelevant, just know that the table has a timestamptz field, let's just name it "ts".

I want to query all entries with following conditions:

• ts is between a date range
• ts is between a time range

E.g. return all entries that are between the 24.01.2022 and the 01.02.2022, and the entries are between 14:00 and 16:00.

I came up with following query:

query GetEntriesBetweenDateRangeAndTimeRange(
    $_start: timestamptz = "2022-08-05T22:00:43.278+00:00"
    $_end: timestamptz = "2022-12-05T22:00:43.278+00:00"
    $_startTime: timestamptz = "14:00:00"
    $_endTime: timestamptz = "16:00:00"
  ) {
    table_name(
      where: {
        _and: [{
          ts: { _gte: $_start, _lte: $_end }
        }, {
          ts: {_gte: $_startTime, _lte: $_endTime}
        }]
      }
    ) {
      // Return fields
    }
  }

However the query returns following error:

{
  "errors": [
    {
      "extensions": {
        "code": "data-exception",
        "path": "$"
      },
      "message": "invalid input syntax for type timestamp with time zone: \"14:00:00\""
    }
  ]
}

As far as I know there should be a type "timetz", but if I try to change $_startTime and $_endTime to timetz, I get an error, that the type is unknown.

Is there a way to cast a timestamptz field to a timetz field?

Would it be possible to create a view, that adds an additional field time, which is populated by the timestamptz field?

Answer 1

After a bit of research, I came to the conclusion, that the easiest way to solve this is to create a view. In postgresql you can extract a timetz field from your timestamp and add it to the view.

Here is an example SQL statement:

CREATE OR REPLACE VIEW "table_name_with_time" AS 
select *, table_name.ts::timetz AS time
from table_name;