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:
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:
npminstall--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 =newApolloServer({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:
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:
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.
Do you use timezones? Let us know in the comments on what data types do you most commonly use for dates/timestamps.