If you have database tables in which you are frequently adding or updating data, then it can be useful to have columns which indicates precisely when a specific record was created and updated.

## Creating Timestamp Columns

Ideally you’d want to add these columns when you create the table.

create table if not exists social (
id          serial primary key,
created_at  timestamptz null default now(),
updated_at  timestamptz null default now()
);


If, however, you’ve already created the table (and potentially started populating it)…

create table if not exists social (
id          serial primary key,
);


… then you can add these columns later.

alter table social add created_at timestamptz null default now();
alter table social add updated_at timestamptz null default now();


With those columns in place, any new record inserted into the table will have both the created_at and updated_at columns set to the precise time at which the record was created. We’re 50% of the way there.

We’ll set up a trigger function which will set the updated_at field.

create or replace function trigger_set_updated_at()
returns trigger as $$begin new.updated_at = now(); return new; end;$$ language plpgsql;


## Pulling the Trigger

And the final step is to attach the trigger function to the table.

create trigger set_timestamp
before update on social
for each row
execute procedure trigger_set_updated_at();


## Testing

The proof is in the pudding, so let’s give this a test. First we’ll insert a record.

insert into social (twitter, linkedin) values ('rbranson', 'rbranson');
select * from social;

id|twitter       |linkedin|created_at                   |updated_at                   |
--+--------------+--------+-----------------------------+-----------------------------+
1|rbranson      |rbranson|2022-02-23 05:39:42.212 +0000|2022-02-23 05:39:42.212 +0000|


Looks good. Note that the created_at and updated_at fields hold the same value. Now we’ll correct the twitter field.

update social set twitter = 'richardbranson' where linkedin = 'rbranson';
select * from social;

id|twitter       |linkedin|created_at                   |updated_at                   |
--+--------------+--------+-----------------------------+-----------------------------+
1|richardbranson|rbranson|2022-02-23 05:39:42.212 +0000|2022-02-23 05:40:51.353 +0000|


The updated_at field has been amended to reflect the time at which the record was altered.

## SQLAlchemy

If you’re using SQLAlchemy then you can add suitable columns to your class definition.

from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, TIMESTAMP

Base = declarative_base()

class Social(Base):
__tablename__ = "social"

id = Column(Integer, primary_key=True)

💡 If, instead of TIMESTAMP, the column is of type Date then use func.current_date() to set its initial value.