Store Timestamps With Time Zone Correctly

A column called created_at looks harmless until two servers, or two offices, disagree about what time it holds. The usual cause is the type. A plain timestamp stores a date and a clock reading with no zone. A timestamptz stores a moment in time. For anything that happened, use the second one. This page shows the difference with real values, and one exception at the end.

Step 1: two columns, one value

SET TIME ZONE 'UTC';
CREATE TABLE meetings (
  meeting_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  plain_ts   timestamp,
  moment     timestamptz
);
INSERT INTO meetings (plain_ts, moment) VALUES ('2026-10-20 09:00+08', '2026-10-20 09:00+08');
SELECT plain_ts, moment FROM meetings;

SET
CREATE TABLE
INSERT 0 1
      plain_ts       |         moment
---------------------+------------------------
 2026-10-20 09:00:00 | 2026-10-20 01:00:00+00
(1 row)

The same text, 09:00+08, went into both columns. The timestamp column ignored the +08 and kept 09:00. The timestamptz column used it: 09:00 in Manila is 01:00 in UTC, and that is what it stored and what a UTC session shows. The plain column now holds a clock reading, and nothing says whose clock it was.

Step 2: the display follows the session

Change the session time zone and read both columns again.

SET TIME ZONE 'Asia/Manila';
SELECT plain_ts, moment FROM meetings;

SET
      plain_ts       |         moment
---------------------+------------------------
 2026-10-20 09:00:00 | 2026-10-20 09:00:00+08
(1 row)

The timestamp value did not move. The timestamptz value is the same moment as before, shown on a Manila clock as 09:00 with a +08 offset. Nothing was changed in the table. Only the display moved.

Step 3: the same moment, shown in other zones

AT TIME ZONE converts a timestamptz to the clock reading in a zone you name, so the answer does not depend on the session.

SELECT moment AT TIME ZONE 'Asia/Manila'      AS manila,
       moment AT TIME ZONE 'America/New_York' AS new_york,
       moment AT TIME ZONE 'UTC'              AS utc
FROM meetings;

       manila        |      new_york       |         utc
---------------------+---------------------+---------------------
 2026-10-20 09:00:00 | 2026-10-19 21:00:00 | 2026-10-20 01:00:00
(1 row)

Each column is a plain timestamp with the clock reading in that zone. Manila is 9 am, New York is 9 pm the evening before, and UTC is 1 am. Use zone names such as Asia/Manila, not fixed offsets such as +08, because names know about daylight saving.

To go the other way, from a clock reading to a moment, use AT TIME ZONE on a plain timestamp: "this reading is in New York".

SELECT TIMESTAMP '2026-10-20 09:00' AT TIME ZONE 'America/New_York' AS moment;

         moment
------------------------
 2026-10-20 21:00:00+08
(1 row)

The session is still on Asia/Manila here, so the moment is shown there: 9 am in New York is 9 pm in Manila.

Step 4: store a moment, not a reading

When you insert a value without an offset, PostgreSQL reads it in the session's zone. The same literal means different moments in different sessions.

SET TIME ZONE 'UTC';
INSERT INTO meetings (moment) VALUES ('2026-10-20 09:00');
SET TIME ZONE 'Asia/Manila';
INSERT INTO meetings (moment) VALUES ('2026-10-20 09:00');
SELECT meeting_id, moment AT TIME ZONE 'UTC' AS utc FROM meetings WHERE plain_ts IS NULL ORDER BY meeting_id;

SET
INSERT 0 1
SET
INSERT 0 1
 meeting_id |         utc
------------+---------------------
          2 | 2026-10-20 09:00:00
          3 | 2026-10-20 01:00:00
(2 rows)

Two inserts of "09:00" are eight hours apart. Always give the offset or the zone when you insert, for example '2026-10-20 09:00+08' or '2026-10-20 09:00 Asia/Manila', and let the application send explicit values.

The exception: a clock time that must stay local

"Every Monday at 9 am in Manila" is a rule about a wall clock, not a moment. If the zone's offset changes, the moment changes with it. Store the wall-clock time and the zone name in separate columns, and convert when you need the moment.

CREATE TABLE weekly_events (
  title     text NOT NULL,
  local_at  timestamp NOT NULL,
  zone_name text NOT NULL
);
INSERT INTO weekly_events VALUES ('Stand-up', '2026-10-19 09:00', 'Asia/Manila');
SELECT title, local_at AT TIME ZONE zone_name AS moment FROM weekly_events;

CREATE TABLE
INSERT 0 1
  title   |         moment
----------+------------------------
 Stand-up | 2026-10-19 09:00:00+08
(1 row)

Related course: SQL Intermediate.