Working with Dates, Time, Timezones in GraphQL and PostgreSQL
Tags: postgres, graphql
Published: 2021-04-05 05:53:56

---
title: Working with Dates, Time, Timezones in GraphQL and PostgreSQL
published: true
date: 2021-03-29 17:32:51 UTC
tags: Postgres,GraphQL
canonical_url: https://hasura.io/blog/working-with-dates-time-timezones-graphql-postgresql/
---

 ![Working with Dates, Time, Timezones in GraphQL and PostgreSQL](https://hasura.io/blog/content/images/2021/03/timezones.png)

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](https://hasura.io/learn/database/postgresql/introduction/) and exposed via GraphQL by Hasura.

![Working with Dates, Time, Timezones in GraphQL and PostgreSQL](https://hasura.io/blog/content/images/2021/03/timezones-1.png)

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](https://hasura.io/learn/database/postgresql/core-concepts/3-postgresql-data-types-columns/) 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.

![Working with Dates, Time, Timezones in GraphQL and PostgreSQL](https://hasura.io/blog/content/images/2021/03/add-action.png)<figcaption>Action with Custom GraphQL Types</figcaption>

### 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](https://github.com/Urigo/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:

- [DateTime](https://www.graphql-scalars.dev/docs/scalars/date-time)
- [Date](https://www.graphql-scalars.dev/docs/scalars/date)
- [LocalDate](https://www.graphql-scalars.dev/docs/scalars/local-date)
- [LocalEndTime](https://www.graphql-scalars.dev/docs/scalars/local-end-time)
- [LocalTime](https://www.graphql-scalars.dev/docs/scalars/local-time)
- [Time](https://www.graphql-scalars.dev/docs/scalars/time)
- [Timestamp](https://www.graphql-scalars.dev/docs/scalars/timestamp)

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;
```
<figcaption>Postgres function to merge date and time</figcaption>
### 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.

<iframe width="200" height="113" src="https://www.youtube.com/embed/ESNjXT5yHU0?feature=oembed" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen=""></iframe><figcaption>Is there a good data type to use for time stamps in Hasura?</figcaption>

Do you use timezones? Let us know in the comments on what data types do you most commonly use for dates/timestamps.