Working with Dates, Time, Timezones in GraphQL and PostgreSQL

Dates and timezones are one of the most commonly used data types in building modern apps. GraphQL comes with default scalar types like Int, Float, String, Boolean and ID. But dates and times have to be defined as custom scalars like Date or timestamp etc. In this post, we will look at the various data/time types available through Postgres and exposed via GraphQL by Hasura.

All the types are Implicitly supported and hence the values for them should be given as a String and Postgres takes care of the rest.

Date

date is a GraphQL custom scalar. Allowed values are yyyy-mm-dd. For example: in a mutation, you would use it in the following format:

mutation {
  insert_users(objects: [{ date: "1996-03-15" }]) {
    returning {
     id
     name
    }
  }
}

Time with time zone

timetz is the GraphQL custom scalar for denoting time of day only, with time zone. Allowed values should be of ISO8601 format (e.g. 17:30:15Z, 17:30:15+05:30, 17:30:15.234890+05:30). For example: in a mutation, you would use it in the following format:

objects: [{ time: "17:30:15+05:30" }]

Timestamp with time zone

To use both date and time, with time zone, there is a GraphQL custom scalar of type timestamptz. Allowed values should be of ISO8601 format (e.g. 2016-07-20T17:30:15Z, 2016-07-20T17:30:15+05:30, 2016-07-20T17:30:15.234890+05:30). For example:

objects: [{ created_at: "2016-07-20T17:30:15+05:30" }]

where created_at is the timestamptz column.

Reusing Postgres types in Custom Code

Hasura lets you write custom code through webhooks in the form of Actions, Remote Schemas and Events. In case of Actions, you can define the custom GraphQL types for the definition.

Actions

Let's define an action for fetching user data.

type Query {
  fetchUser (
    id: Int!
  ): UserOutput
}

The UserOutput will return fields of different date/timestamp variations.

type UserOutput {
  id : Int
  name : String
  created_at : timestamptz
  dob : date
  time_of_activity : timetz
}

Here, created_at is of type timestamptz, dob is of type date and time_of_activity is of type timetz, all reused from Postgres.

Remote Schemas

While creating a remote schema, the date/time values will come from a custom defined GraphQL scalar. You can make use of the community maintained GraphQL scalars.

To start off, install the module:

npm install --save graphql-scalars

and assuming that you are using apollo-server to write your custom GraphQL server, the following code will give you a rough idea.

import { ApolloServer } from 'apollo-server';
import { makeExecutableSchema } from '@graphql-tools/schema';
import { DateTimeResolver, DateTimeTypeDefinition } from "graphql-scalars"

const server = new ApolloServer({
  schema: makeExecutableSchema({
    typeDefs: [
      ...DateTimeTypeDefinition
    ],
    resolvers: {
      ...DateTimeResolver
    },
  }),
});

server.listen().then(({ url }) => {
  console.log(`🚀 Server ready at ${url}`);
});

We are importing a specific scalar, DateTime and using them inside the typeDefs and resolvers. The custom scalars supported by this library around dates / times are:

In your schema, you can return an actual scalar type for the a created_at field on the User type.

type User { 
  id: Int! 
  text: String! 
  created_at: Date! 
}

Using in a computed field

If you are storing date and time values in separate columns and you want to merge them to return a single column, you can create a Postgres function like:

CREATE FUNCTION date_and_time(row, users)
RETURNS TEXT AS $$
  SELECT row.date || ':' || row.time
$$ LANGUAGE sql STABLE;

Postgres function to merge date and time

Creating Postgres views for modifying types

You can make use of views to modify raw strings to actual date/timestamptz values, provided the string conforms to the data type specification of PostgreSQL. For example:

CREATE OR REPLACE VIEW "view_with_timestamp" AS 
 SELECT users.id,
    users.created_string :: timestamptz
   FROM users;

This view contains type cast of string column (created_string) to a timestamptz column. Any string column can be type cast to another data type if the value conforms.

Using server timestamp inside a mutation

Consider having a timestamptz column and typically you would be passing a value from the client. Since it's an implicit type, you will need to send a string value. But the string value can also be a Postgres expression like now(). This will set the value to the server timestamp. For example:

mutation insert_user {
   insert_user_one(object: { id: 1, name: "Praveen", created_at: "now()"
   }) {
     id
   }
}

Querying with ranges

Suppose you have a date range to query, you can do so in Hasura using the $gte and $lte operators.

query {
  users(where: {last_login_date: {_gte: "2021-01-20", _lte: "2021-02-20"}}) {
    id
    name
  }
}

GraphQL Subscription

When you want to subscribe to "events" or "live results" from the backend, typically it will be based on timestamps. For example, if you wanted to listen to a realtime feed updates from the client, you can make a subscription request, ordered by timestamp and sorted by latest desc.

subscription notifyNewData {
    feed (limit: 1, order_by: {created_at: desc }) {
      id
      name
      created_at
    }
  }

Here, the created_at is a timestamptz column sorted by desc and limited by 1 row. This pattern is useful to subscribe to new data for a feed and further making a GraphQL query based on the newly available data.

If you are wondering which data type to use for what use case, check out the video from Adron going over the types in Postgres that's best suited for timestamps.