Learning center

Move from timestamp to timestamptz while old writers send local times

Converting a timestamp column to timestamptz means deciding which time zone each stored value was in. A sync trigger that casts with ::timestamptz uses the time zone of whichever session wrote the row, so two pods with different settings store the same booking an hour apart. Name the zone in the conversion. Even then, local times inside a daylight saving change convert to a time the writer did not mean, and those rows need a check of their own.

The trigger that depends on the session#

Release 2026.11.3 writes starts_local timestamp, the booking's start in Berlin time. Release 2026.11.4 adds starts_at timestamptz and a trigger fills it from the old column. Our server's default TimeZone was Etc/UTC. We inserted the same booking from a pod that set nothing and from one whose environment set PGTZ=Europe/Berlin, on PostgreSQL 16.15:

CREATE FUNCTION bookings_fill() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.starts_at := NEW.starts_local::timestamptz; RETURN NEW; END $$;
idstarts_localstarts_at (shown in UTC)
12026-11-23 09:00:002026-11-23 09:00:00+00
22026-11-23 09:00:002026-11-23 08:00:00+00

Same input, an hour apart. The cast interprets a timestamp in the session's TimeZone, as the date/time types chapter describes, and drivers, connection strings and environment variables can all set that. A rolling deploy whose new pods carry a different environment produces this split without any code change.

Name the zone#

CREATE OR REPLACE FUNCTION bookings_fill() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.starts_at := NEW.starts_local AT TIME ZONE 'Europe/Berlin'; RETURN NEW; END $$;

Both pods then stored 2026-11-23 08:00:00+00. Use the same expression in the backfill. If different rows were written in different zones, the zone has to come from data, such as the venue's zone in another column; a constant would be wrong for some rows.

Local times that do not exist or occur twice#

In Berlin, 02:30 on 29 March 2026 did not happen, because clocks moved from 02:00 to 03:00. On 25 October 2026, 02:30 happened twice. With the zone named:

starts_localstarts_at (UTC)back to Berlin time
2026-10-25 02:30:002026-10-25 01:30:00+002026-10-25 02:30:00
2026-03-29 02:30:002026-03-29 01:30:00+002026-03-29 03:30:00

PostgreSQL picked the second occurrence of the repeated time and moved the missing time forward, following its documented rules for invalid or ambiguous timestamps. The March booking now reads 03:30 in the new release while the old release shows 02:30. Neither is an error. Find these rows before switching readers:

SELECT id, starts_local,
       CASE WHEN starts_at AT TIME ZONE 'Europe/Berlin' <> starts_local THEN 'did not exist, moved'
            ELSE 'occurred twice' END AS problem
FROM bookings
WHERE starts_at AT TIME ZONE 'Europe/Berlin' <> starts_local
   OR (starts_at - interval '1 hour') AT TIME ZONE 'Europe/Berlin' = starts_local;

We added bookings at 01:59 and 03:15 on 25 October and at 01:59 and 03:00 on 29 March, all of which exist once. The query returned only the two problem rows. The second condition finds a repeated time because an instant one hour earlier shows the same Berlin time.

Decide what each one should be with the people who own the bookings; the stored value alone cannot tell you.

The compatibility test#

Insert the same local time through each release's pods, with each pod's real environment, and compare starts_at. Pass when the values agree, and when the moved-or-ambiguous query returns only rows someone has reviewed. Reconciliation for the rest of the table works as in find every backfill mismatch with IS DISTINCT FROM, comparing starts_local AT TIME ZONE 'Europe/Berlin' with starts_at.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading