TopolearnTime zone bugs are among the most common data bugs; one rule about timestamptz prevents most of them.
PostgreSQL has two timestamp types, and the names mislead:
timestamp with time zone(timestamptz) stores an absolute instant, internally in UTC. On input, a value without an offset is interpreted in the session'sTimeZone; on output it is converted to the session'sTimeZone. It does not store the original zone.timestamp(without time zone) stores a wall-clock reading with no zone at all.2026-03-29 02:30means nothing until you say where.
Rule: use timestamptz for events that happened at a moment (created_at, paid_at). Use timestamp or date + time only for civil times that must not shift, such as "the shop opens at 09:00 local time", and store the zone name in another column.
Useful tools:
now()returns the transaction's start time;clock_timestamp()the actual current time.AT TIME ZONE 'Asia/Yerevan'converts a timestamptz into local wall-clock time in that zone (and the reverse for a plain timestamp).intervalarithmetic:now() - interval '7 days',age(a, b).date_trunc('day', ts)for bucketing — it truncates in the session time zone.
Set the server or database TimeZone to UTC and convert at the edges, in the application or with AT TIME ZONE.
Example
SET TimeZone = 'UTC';
CREATE TABLE login (at timestamptz NOT NULL);
INSERT INTO login VALUES ('2026-03-10 18:30:00+04'); -- an instant
SELECT at FROM login; -- 2026-03-10 14:30:00+00
SET TimeZone = 'America/New_York';
SELECT at FROM login; -- 2026-03-10 10:30:00-04 (same instant)
SELECT at AT TIME ZONE 'Asia/Yerevan' FROM login; -- 2026-03-10 18:30:00 (wall clock)
SELECT '2026-01-31'::date + interval '1 month'; -- 2026-02-28 00:00:00
SELECT date_trunc('hour', at) FROM login; -- 2026-03-10 10:00:00-04Remember
- timestamptz stores an instant (UTC) and displays it in the session TimeZone
- Use timestamptz for events; plain timestamp only for zone-less civil times
- AT TIME ZONE converts between instants and local wall-clock times
Watch out for
- Using timestamp without time zone for created_at and getting shifted values when servers or sessions change zone.