Postgres timestamptz traps: BC's permanent DST shift breaks future appointments

British Columbia's decision to stay on Pacific Daylight Time permanently will silently shift stored timestamptz values by an hour for future appointments unless you've updated your tzdata. On March 8, 2026, BC moved clocks to UTC-7 and will not fall back to UTC-8 in November. The America/Vancouver timezone offset is now permanently UTC-7. If you stored a November 2026 appointment as a timestamptz before the tzdata update, your query will return the wrong local time.

Why timestamptz fails when timezone rules change

timestamptz columns do not store local time. They store UTC. The timezone setting at insert time converts the input to UTC. When you read, it converts back using the current timezone rules. If the rules changed between write and read, the local time you get back is not what the user typed.

Crunchy Data's Christopher Winslett gives a concrete example. A user books a 10 AM appointment for November 10, 2026 in Vancouver. You store it with '2026-11-10T10:00:00-08:00' (PST, the old rule). Postgres converts to UTC: 2026-11-10 18:00:00+00. In April 2026, the tzdata package updates with BC's new permanent PDT rule. When you query that row in November, Postgres uses the new America/Vancouver offset (UTC-7) and returns 2026-11-10 11:00:00 - one hour late.

How do you know if your tzdata has updated? Run:

SELECT to_char( '2026-12-01 10:00:00'::timestamp AT TIME ZONE 'America/Vancouver', 'HH24:MI:SS OF' ) AS november_2026_vancouver_offset;

If the offset is 17:00:00 +00, tzdata is current. If 18:00:00 +00, you're still on old rules - and your data is consistent but your system is unaware of the change.

The dual-column pattern that preserves local intent

For events where the wall-clock time is authoritative - appointments, legal deadlines, calendar events - you need to store what the user actually entered, not just the UTC moment. Winslett proposes a three-column schema:

• local_time timestamp NOT NULL - the raw wall-clock value
• timezone_name text NOT NULL - the IANA timezone, e.g. 'America/Vancouver'
• starts_at_utc timestamptz NOT NULL - a trigger-computed UTC timestamp

Because Postgres does not allow generated columns of type timestamptz (it's not immutable for timezone changes), you use a trigger:

CREATE OR REPLACE FUNCTION recompute_appointment_utc() RETURNS TRIGGER AS $$
BEGIN
 NEW.starts_at_utc := NEW.local_time AT TIME ZONE NEW.timezone_name;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ts_recompute_starts_at_utc
BEFORE INSERT OR UPDATE ON appointments
FOR EACH ROW EXECUTE FUNCTION recompute_appointment_utc();

local_time and timezone_name never change except at the user's explicit request. They preserve intent. starts_at_utc is what you index and use for constraints. When tzdata updates, re-querying or re-computing starts_at_utc will reflect the current rules, but local_time remains the canonical original input.

Don't use this pattern for everything. For logs, sensor readings, or financial transactions where the exact UTC moment is authoritative, plain timestamptz is fine. The dual-column pattern adds cost - but for any system that schedules future events across a timezone boundary that might change, it's the only reliable escape hatch.

Ubuntu updates tzdata every few months. If you have customers in British Columbia, check your offset query today. Your November appointments depend on it.

Source: British Columbia, Time Zones, and Postgres
Domain: crunchydata.com