British Columbia, Time Zones, and Postgres

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.