Adding created_at / updated_at timestamps¶
Table of contents
Introduction¶
We often need created_at
and updated_at
timestamp fields in our tables in order to indicate when an object was created or last updated.
This page explains how to add these.
Add a created_at timestamp¶
On the Hasura console, click on the Modify
tab of a table. When clicking on the +Frequently used columns
button,
choose created_at
:
Click the Add column
button.
Create a migration manually and add the following SQL statement to the up.sql
file:
ALTER TABLE ONLY "public"."article" ADD COLUMN "created_at" TIMESTAMP DEFAULT NOW();
Add the following statement to the down.sql
file in case you need to roll back the above statement:
ALTER TABLE article DROP COLUMN created_at;
Apply the migration and reload the metadata:
hasura migrate apply
hasura metadata reload
You can add a created_at
timestamp by using the run_sql metadata API:
POST /v1/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin
{
"type": "run_sql",
"args": {
"sql": "ALTER TABLE ONLY \"article\" ADD COLUMN \"created_at\" TIMESTAMP DEFAULT NOW();"
}
}
Add an updated_at timestamp¶
On the Hasura console, click on the Modify
tab of a table. When clicking on the +Frequently used columns
button,
choose updated_at
:
Click the Add column
button.
Create a migration manually and add the below SQL statement to the up.sql
file:
- Add an
updated_at
timestamp field to thearticle
table. - Define a Postgres function to set the
updated_at
field toNOW()
. - Create a Postgres trigger to call the defined function whenever an article is updated.
ALTER TABLE ONLY "public"."article"
ADD COLUMN "updated_at" TIMESTAMP DEFAULT NOW();
CREATE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_timestamp
BEFORE
UPDATE ON article
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
Add the following statement to the down.sql
file in case you need to roll back the above statement:
DROP trigger set_timestamp on article;
DROP function trigger_set_timestamp();
ALTER TABLE article DROP COLUMN updated_at;
Apply the migration and reload the metadata:
hasura migrate apply
hasura metadata reload
You can add an updated_at
timestamp by using the run_sql metadata API.
The below SQL statement will achieve the following:
- Add an
updated_at
timestamp field to thearticle
table. - Define a Postgres function to set the
updated_at
field toNOW()
. - Create a Postgres trigger to call the defined function whenever an article is updated.
POST /v1/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin
{
"type": "run_sql",
"args": {
"sql":
"ALTER TABLE ONLY \"public\".\"article\"
ADD COLUMN \"updated_at\" TIMESTAMP DEFAULT NOW();
CREATE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_timestamp
BEFORE
UPDATE ON article
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();"
}
}