British Colombia, Time Zones, and Postgres
3 hours ago
- #Data Integrity
- #PostgreSQL
- #Time Zones
- British Columbia moved to permanent Pacific Daylight Time (UTC-7) on March 8, 2026.
- Storing future timestamps in timestamptz columns can cause hour shifts if timezone rules change between storage and query.
- Updated tzdata packages in PostgreSQL reflect new timezone rules, affecting future date calculations.
- The dual-column pattern (local timestamp, timezone, and UTC timestamp) preserves local intent for authoritative future events.
- A trigger can compute UTC timestamps from local time and timezone, with updates needed when tzdata changes.
- RFC 9557 timestamp format does not solve issues from timezone definition changes for future local times.
- If tzdata updates have already occurred, a data correction project is required to fix impacted records.