Splitting a Timestamp with Postgres Generated Columns and GraphQL Query with Hasura
Tags: database, postgres, tutorial, programming
Published: 2021-06-30 05:28:32

---
title: Splitting a Timestamp with Postgres Generated Columns and GraphQL Query with Hasura
published: true
description: Recently I created a video short on how to split out a timestamp column for Hasura. This included the SQL for Postgres via a schema migration and also details on how this appears in the Hasura user interface. You can check out the video here.
tags: database, postgres, tutorial, programming
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/01v8hqgkjkx4t0wb4v6l.png
---
Recently I created a video short on how to split out a timestamp column for Hasura. This included the SQL for Postgres via a schema migration and also details on how this appears in the Hasura user interface. You can check out the video here.

{% youtube i4clYumFacM %}

The break out of what I show in the video is available in a Github repository also.

https://github.com/Adron/graphql-relational-concept-mapping

## Postgres Table Creation SQL

Here is the specific database query that creates the table with the timestamp being broken out to the year, month, and day as generated column data.

``` sql
create table standard_relational_model.users_data
(
    user_id uuid PRIMARY KEY,
    address_id uuid,
    signup_date timestamp DEFAULT now(),
    year int  GENERATED ALWAYS AS (date_part('year', signup_date)) STORED,
    month int  GENERATED ALWAYS AS (date_part('month', signup_date)) STORED,
    day int  GENERATED ALWAYS AS (date_part('day', signup_date)) STORED,
    points int,
    details jsonb
);
```

In this SQL the `signup_date` column is the timestamp columnt that I want split out to year, month, and day. I've set it up with a default function call of `now()` just to seed the column and not require entry when inserting a new row. With that seed, then the generated columns of year, month, and day use the `date_part()` function to extract the particular value out of the `signup_date` column and store it in the respective column.

The other columns are just there for other references.

## The Hasura Console

In the Hasura Console those columns would look something like this.

![Hasura Console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hng1zd82kl3w7hcs4dxh.png)

Notice the syntax displayed for these is different than the migration that created them.

```sql
date_part('day'::text, signup_date)
```

The above of course is for day, and each respective part is designated by month, year, etc.

When the data is added to the table the results return as follow with GraphQL and results.

### GraphQL

The query.

```javascript
query MyQuery {
   users_data {
    signup_date
    year
    month
    day
  }
}
```

The results.

```javascript
{
  "data": {
    "users_data": [
      {
        "signup_date": "1999-04-21T00:00:00",
        "year": 1999,
        "month": 4,
        "day": 21
      },
    
            ... etc ...
            
      {
        "signup_date": "2007-01-02T00:00:00",
        "year": 2007,
        "month": 1,
        "day": 2
      },
      {
        "signup_date": "2021-06-29T00:09:48.359247",
        "year": 2021,
        "month": 6,
        "day": 29
      }
    ]
  }
}
```

### SQL

The query.

```sql
select signup_date, year, month, day
from standard_relational_model.users_data;
```

The results.

```sql
1999-04-21 00:00:00.000000,1999,4,21
2012-07-04 00:00:00.000000,2012,7,4
2019-06-24 00:00:00.000000,2019,6,24
2013-03-07 00:00:00.000000,2013,3,7
2007-01-02 00:00:00.000000,2007,1,2
2021-06-29 00:09:48.359247,2021,6,29
```

That is how to build generated columns in Postgres and how they're available via Hasura to expose via GraphQL!