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 $$;
| id | starts_local | starts_at (shown in UTC) |
|---|---|---|
| 1 | 2026-11-23 09:00:00 | 2026-11-23 09:00:00+00 |
| 2 | 2026-11-23 09:00:00 | 2026-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_local | starts_at (UTC) | back to Berlin time |
|---|---|---|
| 2026-10-25 02:30:00 | 2026-10-25 01:30:00+00 | 2026-10-25 02:30:00 |
| 2026-03-29 02:30:00 | 2026-03-29 01:30:00+00 | 2026-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.