Adding Timestamp Columns

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,
	twitter     varchar(15),
	linkedin    varchar(29),
	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,
	twitter     varchar(15),
	linkedin    varchar(29)
);

… 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.

Trigger for Updates

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)
    twitter = Column(String(15))
    linkedin = Column(String(29))

    created_at = Column(TIMESTAMP, server_default=func.now())
    updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.current_timestamp())

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

Conclusion

This simple approach allows you to keep track of when records were created or updated. If you need to be more granular (like knowing which fields was updated and when) then you need to work a bit harder. But this already adds some very useful metadata.