HN Debrief

British Columbia, Time Zones, and Postgres

  • Programming
  • Databases
  • Infrastructure

The post walks through a nasty but common edge case in time handling: British Columbia plans to stop switching clocks, which means a future appointment booked under today’s rules can map to a different UTC instant after the law changes. In Postgres terms, the warning is that `timestamptz` is great for recording an instant, but not always enough for representing a future event whose meaning is tied to a place’s wall clock. The practical fix is to model the thing you actually promised. If the promise is “2 PM at the Vancouver dentist,” store that local date and time with the relevant zone, then resolve it using current zone rules when needed.

Audit any system that stores future scheduled events as only UTC or Unix time. If the business promise is about local wall-clock time, keep the local date, local time, and time-zone identifier as first-class data and decide explicitly whose clock is authoritative.

Discussion mood

Mostly appreciative and practical. People agreed the post highlights a real footgun, and the strongest reactions were aimed at oversimplified advice like “just store UTC,” which many saw as wrong for future local events. There was also side chatter from British Columbia residents grumbling about the policy choice itself.

Key insights

  1. 01

    Model future appointments as wall time

    For bookings that are meant to happen when a local clock reads a certain value, preserve the local date and local time as the source of truth. A plain `TIMESTAMP` without time zone can carry that wall time cleanly in the database, while derived epoch values or indexes can still support fast queries without becoming authoritative.

    Treat future scheduling tables differently from event logs. Keep an explicit local-time representation that matches the business promise, then add computed UTC or epoch fields only for search, sorting, or interoperability.

      Attribution:
    • jagged-chisel #1
    • mulmen #1
  2. 02

    Pick the authoritative clock up front

    The hard part is not storage format alone. It is deciding whose clock the system is honoring. A dentist appointment usually follows the office’s local wall clock. A virtual meeting across regions might need to stay pinned to participant A, participant B, or a fixed instant. If you do not choose, a legal time-zone change will choose for you and one side will feel cheated.

    Make “authoritative clock” an explicit product decision for every scheduling feature. Write it into API contracts and user-facing behavior before you pick data types.

      Attribution:
    • Xirdus #1 #2
    • Terr_ #1
    • Mogzol #1
  3. 03

    British Columbia is not one time zone

    Using “BC” as a single scheduling region will already fail in parts of the province. The southeast and northeast have different practices, and some communities have historically stayed on standard time year-round. The real key is a concrete IANA zone like `America/Vancouver`, not a province or a vague “Pacific time” label.

    Store precise geographic time-zone identifiers, not state or province names and not informal labels like PST. Review any code or config that assumes a political region maps to one zone.

      Attribution:
    • rjrjrjrj #1
  4. 04

    Fallback hours stay ambiguous

    When clocks move back, a local time like 1:30 AM can refer to two real instants. Storing only wall time leaves that unresolved. Adding the UTC offset or another disambiguator can tell you which occurrence was intended, though business logic still has to decide what to do when rules later change again.

    If your product schedules anything during DST transitions, add tests and schema support for repeated and skipped local times. Do not assume users or operators will avoid those hours forever.

      Attribution:
    • ncruces #1 #2
    • rawling #1
  5. 05

    Tzdata corrections can rewrite history

    Past timestamps are not immune from trouble because the time-zone database itself can be corrected after the fact. Moldova’s transition times were adjusted in tzdata 2026a for years that had already passed. That means converting a stored UTC instant back to local wall time can produce a different answer after an update. Storing wall time and zone preserves what the user saw, while storing UTC preserves the physical instant. They solve different problems.

    For records where both the observed wall time and the actual instant matter, store both. If auditability is important, consider recording the zone identifier and possibly the tzdata version used at capture time.

      Attribution:
    • dqv #1 #2
    • drdexebtjl #1

Against the grain

  1. 01

    UTC as the only source of truth

    This view holds that a Unix timestamp is the only clean fact because it is just a monotonic count for a real instant. Everything else is presentation. That framing works well for logs, metrics, and machine events, but it breaks down when the promised behavior is anchored to a future local clock reading rather than to a fixed instant.

    Keep this model for machine-time data. Do not extend it to human scheduling without checking whether the product promise is really about an instant or about local wall time.

      Attribution:
    • necro #1
  2. 02

    Keep time-zone logic out of the database

    A stricter camp argued that location-bound appointments should live as SQL `DATE` and `TIME`, with conversion deferred entirely to the presentation layer. The useful kernel here is separation of concerns. The weaker part is pretending the database can stay oblivious, because persistence still needs enough information to reconstruct the intended local event correctly.

    Separate display conversion from storage rules, but do not strip the database of time semantics. The schema still needs to encode locality and intent, not just a formatted clock reading.

      Attribution:
    • ivan_gammel #1 #2
  3. 03

    Freeze behavior with tzdata versions

    One suggestion was to store the tzdata version alongside timestamps so future decoding can use the same rule set that was in effect when the value was written. That would preserve historical interpretation, but it also locks in mistakes when tzdata later fixes past errors. Several other comments implied that this is useful for audit trails, not as a universal answer.

    Consider storing tzdata version only when reproducibility matters, such as audits or forensics. For user-facing scheduling, you usually still need a strategy for applying corrected zone rules.

      Attribution:
    • mulmen #1

In plain english

IANA zone ↩
A standard named time-zone identifier like `America/Vancouver` from the Internet Assigned Numbers Authority database.
Postgres ↩
Short for PostgreSQL, an open source relational database system.
timestamptz ↩
PostgreSQL’s timestamp-with-time-zone type, which stores a specific instant in time and converts it for display using time-zone rules.
tzdata ↩
The standard time-zone database used by many operating systems and programming libraries to map places and dates to UTC offsets and daylight-saving rules.
Unix timestamp ↩
A numeric count of seconds or milliseconds since 1970-01-01 00:00:00 UTC, used to represent an exact instant.
UTC ↩
Coordinated Universal Time, the global reference time standard used to represent exact instants independent of local clock rules.

Reference links

Projects and tools

  • pg_temporal
    A PostgreSQL-focused attempt to model temporal data and the scheduling issues raised by the post.