Postgres Date/Time types on GraphQL
Date/Time types are one of the basic types every database supports, and Postgres has robust support for a large set of date and time types, as shown below:
This allows for a fair amount of flexibility in how time/date can be stored in Postgres. This blog post lists some examples on using these date/time types in GraphQL using the Hasura GraphQL Engine (HGE), an open source tool that gives instant GraphQL APIs over Postgres.
Date/Time Inputs
Postgres is very flexible in handling date/time inputs, and accepts several possible input methods.
For the following examples, we focus on the ISO-8601 representation of time, and the Postgres timestamptz type — Eg. “2018–08–10T14:14:57+05:30”
Date/Time inputs in HGE
The Hasura Graphql Engine supports the following date-time types:
These consist of implicit timestamp types that can be used as strings in queries, or properly validated custom Graphql scalars Timetz and Timestamptz, which allow only properly formatted ISO-8601 values.
HGE also supports creating columns that can have default timestamp values inserted at creation.
While creating your schema, you can create a Timestamp column, and use a function to set it on object insertion:
Timestamptz vs Timestamp
The timestamptz field in Postgres is basically just the timestamp field where Postgres actually just stores the “normalised” UTC time, even if the timestamp given in the input string has a timezone.
If your input string is: 2018-08-28T12:30:00+05:30 , when this timestamp is stored in the database, it will be stored as 2018-08-28T07:00:00.
The advantage of this over the simple timestamp field is that your input to the database will be timezone independent, and will not be inaccurate when apps from different timezones insert timestamps, or when you move your database server location to a different timezone.
To quote from the docs:
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
To give a simple analogy, a timestamptz value represents an instant in time, the same instant for anyone viewing it. But a timestamp value just represents a particular orientation of a clock, which will represent different instances of time based on your timezone.
For pretty much any use case, timestamptz is almost always a better choice. This choice is made easier with the fact that both timestamptz and timestamp take up the same 8 bytes of data.
Querying Date Time
For the next couple of sections, we’ll be using HGE, with the following schema:
In this table, the id is an auto-incrementing integer (Though you might prefer using UUIDs, here’s our thoughts on why), and the created column is a timestamptz field that gets filled with the timestamp at the instant of insertion. The edited field is another timestamptz field, but can be inserted into.
Querying date time types is as simple as representing them as ISO-8601 formatted strings.
Inserting into Date/Time fields
Inserting into a Timestamptz field in HGE is as simple as a normal insert, with the timestamp as a string:
You can also insert just the date:
Or the timestamp without the timezone:
You can try out all the above examples on a live HGE instance here.
References:
