Postgres: Adding created_at / updated_at Timestamps

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

Console

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.

CLI

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

API

You can add a created_at timestamp by using the run_sql schema API:

POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
      "source": "<db_name>",
      "sql": "ALTER TABLE ONLY \"article\" ADD COLUMN \"created_at\" TIMESTAMP DEFAULT NOW();"
  }
}

Add an updated_at timestamp

Console

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.

CLI

Create a migration manually and add the below SQL statement to the up.sql file:

1. Add an updated_at timestamp field to the article table.
2. Define a Postgres function to set the updated_at field to NOW().
3. 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

API

You can add an updated_at timestamp by using the run_sql schema API.

The below SQL statement will achieve the following:

1. Add an updated_at timestamp field to the article table.
2. Define a Postgres function to set the updated_at field to NOW().
3. Create a Postgres trigger to call the defined function whenever an article is updated.

POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
      "source": "<db_name>",
      "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();"
  }
}