Postgres timestamp vs timestamptz: the DST bug that only breaks twice a year

A team I know spent the better part of two months chasing a ghost. Twice a year, a handful of customers reported that their appointments had shifted by exactly one hour. Not all appointments, not all customers, and never on a day anyone could predict. They rewrote date handling in the frontend. They swapped calendar libraries. They added logging everywhere. The bug came back on the next clock change and laughed at them.

The root cause was one word in a schema they had read a hundred times: the column was timestamp, not timestamptz. That is the entire bug. A timestamp without time zone column stores the number on the clock and throws away the context, so at a daylight saving transition the same stored value maps to a different real instant depending on who is reading it and when.

This class of bug is nearly invisible in code review, because the column type gives no signal that anything is wrong, and it only fires at DST boundaries, which makes it almost impossible to reproduce on demand. Here is how to identify it, fix it, and migrate safely without corrupting the data you already have.

What the two types actually store

The names are misleading, which is most of the problem. Neither type stores a time zone.

• timestamp (short for timestamp without time zone) stores a literal date and time, like a photo of a wall clock: 2026-03-29 02:30:00. Postgres keeps exactly those digits. It has no idea whether that is Nairobi time, London time, or UTC. When you read it back, you get those same digits regardless of your session.
• timestamptz (short for timestamp with time zone) stores an absolute point in time. On write, Postgres reads your session's time zone, converts the value to UTC, and stores that. On read, it converts from UTC back into your session's time zone. The value is a fixed instant; only its presentation changes.

You can watch the difference in one session. Set a time zone, insert the same string into both column types, then read from a different time zone:

CREATE TABLE demo (naive timestamp, aware timestamptz);
SET TIME ZONE 'Africa/Nairobi';          -- UTC+3
INSERT INTO demo VALUES ('2026-06-01 12:00', '2026-06-01 12:00');

SET TIME ZONE 'UTC';
SELECT * FROM demo;
--        naive         |         aware
-- 2026-06-01 12:00:00  | 2026-06-01 09:00:00+00

The naive column still reads 12:00 in UTC, which is wrong: the appointment was booked for noon in Nairobi, which is 09:00 UTC. The aware column knows this and shows 09:00. The plain timestamp lost three hours of meaning the moment it was stored.

Why it only breaks twice a year

If your servers, your database session, and your users all sit in a zone that never changes its offset, a timestamp column can appear to work forever. Kenya, for example, has been on UTC+3 with no daylight saving for decades, so a Nairobi-only app can run on naive timestamps for years and never see the bug. The trap springs the moment a value is written under one offset and read under another.

Daylight saving is the most common way that happens without anyone changing code. A customer in Berlin books an appointment in October under CET (UTC+1). The clocks go forward in March to CEST (UTC+2). Your naive timestamp still holds the same wall-clock digits, but the code that renders it now assumes a different offset, and the appointment jumps by an hour. It affects only records that straddle a transition, only for zones that observe DST, and only twice a year. That is exactly the signature that makes it so hard to catch: rare, seasonal, and untriggerable on the day someone finally sits down to debug it.

How to tell which type you have

You cannot see this in most ORM model files, and you cannot see it in application code at all. Ask the database directly:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type LIKE 'timestamp%'
ORDER BY table_name, column_name;

The data_type will read either timestamp without time zone or timestamp with time zone. Every row that says "without time zone" and holds a moment that different users in different zones will read is a candidate for this bug. A column that only ever holds a date-like local value with no cross-zone meaning (a store's published opening hour, say) may legitimately be naive, but that is the rare case, not the default.

The fix, step by step

Step 1: Make new columns timestamptz by default

Stop the bleeding first. For any new column, use timestamptz:

CREATE TABLE appointments (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    starts_at  timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Note now() returns a timestamptz, which is what you want for audit columns. If you had used timestamp here, the default would depend on the server's session zone forever.

Step 2: Find out what zone your existing naive values are really in

This is the step you cannot skip and cannot guess. A naive column has no recorded zone, so to convert it you must know the assumption the writing code was making. Usually it is one of: the database server's local zone, the application server's zone, or UTC. Check how the values were written. If your app called datetime.utcnow() or ran with PGTZ=UTC, the stored digits are UTC. If it wrote local time in Nairobi, they are Africa/Nairobi. Get this wrong and you will shift every historical row by a fixed offset, turning a twice-a-year bug into a permanent one.

Step 3: Alter the column with an explicit source zone

Once you know the source zone, convert in place. The USING clause with AT TIME ZONE tells Postgres how to interpret the naive digits before storing the resulting instant:

-- The stored digits were UTC:
ALTER TABLE appointments
    ALTER COLUMN starts_at TYPE timestamptz
    USING starts_at AT TIME ZONE 'UTC';

-- The stored digits were Nairobi local time:
ALTER TABLE appointments
    ALTER COLUMN starts_at TYPE timestamptz
    USING starts_at AT TIME ZONE 'Africa/Nairobi';

The subtlety of AT TIME ZONE is that it does opposite things depending on the type it is applied to. Applied to a naive timestamp, it means "interpret these digits as being in this zone" and produces a timestamptz. Applied to a timestamptz, it means "show me this instant as it looks in that zone" and produces a naive timestamp. In a migration you are doing the first one. Test it on a copy first; on a large table this rewrites every row and takes a lock, so schedule it like any other heavy migration.

Verification

Confirm the type actually changed and a known value now behaves correctly across zones:

-- 1. The type is now timestamptz
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'appointments' AND column_name = 'starts_at';
--  starts_at | timestamp with time zone

-- 2. A known appointment reads correctly in two zones
SET TIME ZONE 'Africa/Nairobi';
SELECT starts_at FROM appointments WHERE id = 1;   -- e.g. 12:00 local
SET TIME ZONE 'UTC';
SELECT starts_at FROM appointments WHERE id = 1;   -- 09:00 UTC, same instant

If the same row reads as the same absolute instant expressed in two different zones, the column is now storing meaning, not just digits. That is the state you want.

What people get wrong

Blaming the calendar library. This is the two-month trap. The frontend and the date library are downstream of a column that never held enough information to be correct. No amount of frontend work fixes a storage layer that discarded the time zone. Check the column type before you touch a single line of rendering code.

Assuming timestamptz "stores the time zone". It does not. It stores UTC and converts on read. If you genuinely need to remember that an event happened at "9am in Tokyo" as a fact about Tokyo, store the instant in a timestamptz and the zone name in a separate text column. One column cannot do both.

Mixing the two types in one schema. Comparing a timestamp with a timestamptz forces an implicit conversion using the session zone, which is exactly the silent, session-dependent behaviour you are trying to escape. Pick timestamptz and make the whole schema consistent. This is the same category of foot-gun as reaching for chmod 777 to fix a permissions error: it makes the symptom vanish while leaving the real defect in place.

Setting the server to a local zone to "fix" it. Changing timezone in postgresql.conf or the container's TZ only changes how naive values are interpreted at the session boundary. It papers over the problem for one deployment and reintroduces it the moment a replica, a backup restore, or a developer laptop runs under a different zone.

When it is still broken

• Values are now off by a constant offset. You guessed the source zone wrong in Step 2. If every row is shifted by the same number of hours, re-run the conversion from a backup with the correct source zone. This is why you test on a copy.
• Your ORM still writes naive datetimes. A correct column does not save you if the application hands Postgres a Python datetime with no tzinfo. Make your application produce timezone-aware datetimes (in Python, use datetime.now(timezone.utc), not datetime.utcnow()) so the value arriving at the database is unambiguous.
• Reports disagree with the app. A BI tool connecting with a different session zone will render timestamptz values in its own zone. That is correct behaviour, not a bug, but it surprises people. Set an explicit zone in the reporting connection.
• You need date-only grouping "by local day". Group with an explicit conversion: date_trunc('day', starts_at AT TIME ZONE 'Africa/Nairobi'). Grouping a timestamptz without naming a zone groups by UTC days, which splits a local day across two buckets.