Should I use timestamp or timestamptz in Postgres?

timestamp without time zone

Note: this works and blocks nothing, but a performance regression is likely.

What happens

Plain timestamp stores wall-clock digits with no zone: the moment it crosses a server, client, or DST boundary, the same value means different instants. timestamptz stores an absolute instant and costs the same 8 bytes.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

CREATE TABLE events (occurred_at timestamp);

The safe pattern

Use timestamptz: it stores an absolute instant (normalized to UTC) in the same 8 bytes and converts on display. Plain timestamp is only right when you truly mean zone-less wall-clock digits, like future local-time schedules.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

timestamp

CREATE TABLE events (id bigint PRIMARY KEY, occurred_at timestamp);

Stays silent (1)

timestamptz

CREATE TABLE events (id bigint PRIMARY KEY, occurred_at timestamptz);

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV039 BV041 →