British Columbia, Time Zones, and Postgres | Crunchy Data Blog
Christopher Winslett
Jun 16, 2026·6 min read
More by this author
Latest Articles<br>British Columbia, Time Zones, and Postgres<br>Postgres Serials Should be BIGINT (and How to Migrate)<br>Postgres 18 New Default for Data Checksums and How to Deal with Upgrades<br>PostGIS Performance: Simplification<br>Postgres Scan Types in EXPLAIN Plans
Production Postgres<br>British Columbia, Time Zones, and Postgres
Christopher Winslett
Jun 16, 2026·6 min read·More by this author
On March 8, 2026, British Columbia moved their clocks to a year-round Pacific Daylight Savings Time. In March, they did the spring forward one hour with their clocks to UTC-7, but they won't fall back to UTC-8 in November. Going forward, the UTC offset for America/Vancouver timezone is permanently UTC-7.Let's use this as an opportunity to talk about date and time zone storage. In the most basic examples, the default is to store the UTC value, then calculate local time relative to UTC. However, people using calendar systems think in terms of local time (i.e. wall clock time), and never consider UTC. After modifying time zone data, these time calculations from UTC for a region will differ from the user's input value.If you stored timestamps in a UTC-based column for British Columbia-based appointment in 2026 and beyond, your November through March appointments may be off by an hour! See timestamptz columns don't store the local time. They store the UTC time, and the timezone is only used to convert to and from UTC when inserting and querying. If you stored a future appointment as a timestamptz in the America/Vancouver timezone, it was converted to UTC using the rules at the time of storage. When you query that appointment later, it converts back to local time using the current rules. If the rules changed from storage to query, the local time you get back is not what the user originally intended.If you've not updated your tzdata package, then Postgres doesn't know about the change, and it will continue to convert using the old rules. How often are the tzdata packages in Ubuntu updated? Surprisingly, every few months.If your columns are stored in timestamptz column types and work with customers in British Columbia, use the following SQL query to determine if the tzdata package has been updated:SELECT<br>to_char(<br>'2026-12-01 10:00:00'::timestamp AT TIME ZONE 'America/Vancouver',<br>'HH24:MI:SS OF'<br>) AS november_2026_vancouver_offset;<br>If the value is 17:00:00 +00, then tzdata has been updated. This is not as good as it sounds because it will require digging through logs to know if future appointments were created before or after the the timezone adjustment.If the value is 18:00:00 +00, then good news! Your tzdata has not been updated, and you do not have data split over the updates.An Example of the Timezone Shift<br>Earlier this year, a user booked a 10 AM appointment for November 10, 2026 in Vancouver. You store it as a timestamptz:INSERT INTO appointments (patient_id, starts_at)<br>VALUES (42, '2026-11-10T10:00:00-08:00');<br>-- stored as: 2026-11-10 18:00:00+00 (UTC)<br>In April 2026, the tzdata update is released to push the new timezone rules.On November 10, 2026, the patient shows up at 10 AM local time as they documented in their calendar. But when you query the appointment, it says their appointment is at 11 AM local time:SELECT starts_at AT TIME ZONE 'America/Vancouver' AS local_time<br>FROM appointments<br>WHERE patient_id = 42;<br>-- returns: 2026-11-10 11:00:00<br>Notice it is calculated as an hour later than originally entered.A schema that survives time zone changes: dual column pattern<br>As its name implies, a dual-column pattern stores data in two columns (actually three):local timestamplocal timezoneUTC timestampThe UTC timestamp column should be a calculated column. Use the timestamp and timezone to calculate UTC. That calculated UTC value would also be stored and queried to enable background jobs to send notifications and simplify constraint checking, like appointment collisions.The dual-column pattern is necessary when the local intent is authoritative: people or deliveries at a time and place, legal deadlines, calendar events, etc.Don't go overboard though. When the event is in the past, or the exact UTC moment is authoritative (log entries, financial transactions, sensor readings), use plain timestamptz. The dual-column pattern adds cost and complexity only worth paying when future local intent must be preserved.The detailed schema would look like this:CREATE TABLE appointments (<br>id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,<br>local_time timestamp NOT NULL, -- wall clock value<br>timezone_name text NOT NULL, -- IANA name: 'America/Vancouver'<br>starts_at_utc timestamptz NOT NULL -- Calculated via trigger<br>...<br>);<br>local_time and timezone_name together answer the "what did the user intend?" by storing the wall-calendar / wall-clock values / wall-clock location. These values should only change at the user's...