Timestamp Splitting with Postgres Generated Columns and GraphQL Query Access with Hasura

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.

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
);

The Hasura Console

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

date_part('day'::text, signup_date)

GraphQL

The query.

query MyQuery {
users_data {
signup_date
year
month
day
}
}
{
"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.

select signup_date, year, month, day
from standard_relational_model.users_data;
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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Adron Hall

Adron Hall

Software dev, data, heavy metal, transit, economics, freethought, atheism, cycling, livability, beautiful things & adrenaline junkie.