British Columbia, Time Zones, and Postgres

Original Article ↗ Hacker News Discussion ↗

British Columbia’s move to permanent daylight saving time is prompting engineers to re‑examine how they store dates and times in systems like Postgres. Commenters debate whether future events (like medical appointments or meetings) should be stored as UTC timestamps, as local wall-clock time with an associated IANA timezone, or with additional metadata (location, tzdata version, offsets) to stay correct when laws or DST rules change. The broad consensus is to use battle-tested timezone libraries, treat future and past events differently, and accept that no schema can make future time completely stable when governments keep rewriting the rules.

Storing timestamps: UTC vs local time

  • Strong disagreement over “always store UTC.”
  • One camp says: store only UTC Unix timestamps as the source of truth; convert with trusted libraries; timezones are too complex to handle manually.
  • Others argue this is a “common mistake” when wall clock time matters (appointments, punch-ins, office hours). Local time exists independently of UTC and often should be stored directly.
  • Some stress that UTC is appropriate for literal instants (logs, events that already occurred), not for domain concepts tied to local clocks.

Future events and timezone changes (BC case)

  • Core issue: a future appointment set as “2pm local time” can map to a different UTC instant if laws change (e.g., British Columbia moving to permanent daylight time).
  • This forces a choice of “authoritative” reference:
    • The local wall time at the event location, or
    • The originally computed UTC instant, or
    • Another participant’s local time in multi-timezone meetings.
  • Several commenters emphasize that any representation of future events is inherently provisional; governments and borders can change.

Schema and modeling strategies

  • Proposed approaches for future events:
    • Store local date/time + IANA timezone (e.g., America/Vancouver), optionally plus location.
    • For appointments, use DATE/TIME or TIMESTAMP-without-timezone for local time; convert only in the presentation layer.
    • Keep an additional UTC instant for queries/optimization, but clearly mark which field is authoritative.
  • For past events: many suggest UTC, with timezone only when local wall time is semantically important (e.g., time clocks, historical analysis).
  • Some suggest storing timezone offsets and/or tzdata version or insertion time to reconstruct intent when rules or data change.

Libraries, tzdata, and complexity

  • Consensus: never reimplement timezone logic; rely on tzdata-backed libraries.
  • However, tzdata itself changes frequently for both past and future timestamps, and can contain historical errors.
  • Ideas floated: systems that track tzdata versions per record, or use bitemporal-like strategies for “what we believed when.”

DST, policy, and BC reaction

  • Many criticize DST generally and favor permanent time, but disagree whether permanent standard or daylight time is healthier.
  • British Columbia’s decision to adopt permanent daylight time is contentious; some residents like ending clock changes, others argue the consultation was misleading and standard time would be better.