TIMESTAMP vs TIMESTAMPTZ: The Postgres Bug You Will Eventually Inherit

A company I worked with once had a recurring bug where one customer's appointments would shift by an hour twice a year. They had spent two months blaming the calendar library before someone looked at the database schema and realized the column was timestamp without time zone.

If you ever inherit a Postgres schema with timestamp columns instead of timestamptz, this is your future. Twice a year, a percentage of your data is wrong by an hour, and nobody can quite explain why.

Here is the actual difference, why one is almost always right, and how to migrate from the wrong one if you have to.

What each type actually stores

The naming is genuinely confusing. The reality is the opposite of what most developers expect.

timestamp (also timestamp without time zone): stores 8 bytes representing a clock reading. No timezone. The value 2025-01-15 14:00:00 means "two in the afternoon on January 15" with no information about what part of the world. When you read it back, Postgres just gives you the same numbers.

timestamptz (timestamp with time zone): stores 8 bytes representing a UTC instant. When you write 2025-01-15 14:00:00 from a session set to America/Los_Angeles, Postgres converts to UTC and stores the UTC value. When you read it back from a session in Europe/Berlin, Postgres converts to Berlin time. The stored value is always UTC; the conversion is at read/write time.

Why timestamptz is almost always right

Three reasons:

1. The same instant is the same instant. A user in Tokyo and a user in New York looking at "when this order was placed" should see the same moment in their respective timezones. timestamptz does this automatically. timestamp requires the application to remember which timezone the value was stored in and convert manually, every time.

2. DST transitions just work. Twice a year, clocks move. With timestamptz, this is invisible — the stored UTC instant is unchanged, and conversions reflect the new local offset. With timestamp, the application has to handle DST itself, which usually means it does not, and bugs cluster around the second Sunday of March.

3. Indexes and comparisons are unambiguous. timestamptz < now() is a question about absolute time. timestamp < now() is a question about... whatever timezone the session happens to be in, which is a property of the connection, not the data.

The only legitimate use for timestamp without time zone is when you genuinely mean "this clock reading," with no timezone. "The store opens at 9 a.m. local time" is a time (or sometimes a calendar literal). "Daily 3 a.m. cron in this server's timezone" is one of the few cases where timestamp makes sense, and even then most teams should be storing the UTC time and converting at display.

What now() actually returns

SELECT now(); -- 2025-01-15 14:00:00.123456+00

now() returns timestamptz. If you store its value into a timestamp column, Postgres silently converts to the session's timezone interpretation, which is whatever SET TIMEZONE TO ... was last set. This is the source of an enormous amount of confusion.

If you write code that does INSERT INTO events (created_at) VALUES (now()) and created_at is timestamp (not timestamptz), the meaning of the stored value depends on the session timezone. Move the application to a different timezone and the same code stores different values for the same moment.

Migrating from timestamp to timestamptz

On a small table:

ALTER TABLE events
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'UTC';

The USING clause is critical: it tells Postgres what timezone the existing values are in. If they were stored as UTC (the application was disciplined), use 'UTC'. If they were stored as the application's local time, use that. If they are mixed — some local, some UTC, some historical — you have a different and harder problem.

For a large table, the column-rewrite ALTER is expensive. The non-blocking version is to add a new column, backfill, swap, drop:

ALTER TABLE events ADD COLUMN created_at_utc timestamptz;

UPDATE events
SET created_at_utc = created_at AT TIME ZONE 'UTC'
WHERE created_at_utc IS NULL
  AND id BETWEEN $start AND $end;

-- After backfill is done and application writes both columns:
ALTER TABLE events DROP COLUMN created_at;
ALTER TABLE events RENAME COLUMN created_at_utc TO created_at;

Multi-week project on a large hot table, but doable.

What I do for new schemas

For any new column representing a moment in time, I use timestamptz, default now(), and never look back. The 8 bytes are the same as timestamp. The mental overhead saved is enormous.

The only place I would use timestamp without time zone is when storing a value that genuinely has no timezone — a recurring clock reading, a contractual time of day. Even then, I usually find the cleaner solution is to store the UTC instant and the display timezone separately, so that DST and timezone moves are explicit rather than implicit.