PostgreSQL timestamps and time zones

timestamp is not timestamptz

timestamp without time zone stores date and time fields without a time zone. timestamp with time zone, abbreviated as timestamptz, represents an instant. The original geographical time zone is not retained in this value.

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at timestamptz NOT NULL
);
INSERT INTO events (occurred_at)
VALUES ('2024-04-01T14:00:00+02:00');

Convert to and from Unix

SELECT to_timestamp(1711972800) AT TIME ZONE 'UTC';
-- 2024-04-01 12:00:00

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-04-01T14:00:00+02:00');
-- 1711972800.000000

Interpret a local date

If a column without a time zone is known to represent Paris time, AT TIME ZONE 'Europe/Paris' interprets it in that time zone. Check ambiguous times during clock changes before a migration.

SELECT TIMESTAMP '2024-04-01 14:00:00'
       AT TIME ZONE 'Europe/Paris';

The timestamptz result is displayed according to the session time zone. Setting SET TIME ZONE 'UTC' makes diagnosis easier; it does not change the stored instant.

References

Reference documentation

Open the converter

English version published September 26, 2026.