British Columbia, Time Zones, and Postgres • Hacker News Board

Re: British Columbia, Time Zones, and Postgres

#111

Post by Veserv » Tue, Jun 23, 2026, 7:07 PM UTC

Xirdus wrote: post #72

drdexebtjl wrote: post #56

Earlier quoted context omitted.

tzdata doesn’t change retroactively [1]. If an employee clocks in at 2026-06-22 09:00 America/Sao_Paulo time, (which has a -03:00 offset today), and the server's clock is in UTC, the server will save 2026-06-22 12:00 to the database. If America/Sao_Paulo changes to -02:00 on 2027, it doesn’t affect conversions for past dates. You still get 2026-06-22 09:00 when trying to convert 2026-06-22 12:00 to local time in Amer…

But which tzdata? Do you have the timezone or do you not have the timezone? If you have the timezone then why is your timestamp in UTC and not in the timezone that you have to store alongside the UTC timestamp?

No tzdata. When a time becomes the past you canonicalize it to the UTC, or better yet TAI, second it occurred at. As in, this occurred N cesium atom vibrations after the zero timestamp.

That is permanently fixed and can be losslessly and perfectly converted to the corresponding date in the past if you care about what local time was at that instant at some location.

Re: British Columbia, Time Zones, and Postgres

#112

Post by dqv » Tue, Jun 23, 2026, 7:20 PM UTC

pphysch wrote: post #105

dqv wrote: post #73

Earlier quoted context omitted.

> In all likelihood this will never happen in a particular timezone. You sure about that? https://lists.iana.org/hyperkitty/list/tz-announce@iana.org/... 2026b - changes to future timestamps 2026a - changes to past and future timestamps 2025c - changes to past timestamps 2025b - changes to past timestamps 2025a - changes to future timestamps 2024b - changes to past timestamps 2024a - changes to future timestamps 2023…

These aren't global timezone changes, these are changes to individual or small batches of timezones. If you are not scheduling future events in these timezones, they do not affect you. You are welcome to overengineer systems to possibly prevent potential future timezone-shift-caused data corruption. Unless I ran a globally distributed appointment/event database, I would personally avoid doing that.

"overengineering"

    UPDATE appointments
    SET starts_at_utc = local_time AT TIME ZONE timezone_name
    WHERE timezone_name = 'America/Vancouver'
      AND starts_at_utc > now();

Re: British Columbia, Time Zones, and Postgres

#113

Post by pphysch » Tue, Jun 23, 2026, 8:41 PM UTC

dqv wrote: post #112

pphysch wrote: post #105

Earlier quoted context omitted.

These aren't global timezone changes, these are changes to individual or small batches of timezones. If you are not scheduling future events in these timezones, they do not affect you. You are welcome to overengineer systems to possibly prevent potential future timezone-shift-caused data corruption. Unless I ran a globally distributed appointment/event database, I would personally avoid doing that.

"overengineering" UPDATE appointments SET starts_at_utc = local_time AT TIME ZONE timezone_name WHERE timezone_name = 'America/Vancouver' AND starts_at_utc > now();

You elided the part where you are adding an extra column to your database just for this rare corner case.

Re: British Columbia, Time Zones, and Postgres

#114

Post by jagged-chisel » Tue, Jun 23, 2026, 9:41 PM UTC

infamia wrote: post #78

jagged-chisel wrote: post #19

Future events: store the local (at the event) date and time and timezone. You’ll keep the right context even if lawmakers decide to switch things up. You want to see your doctor at 8:30 AM on Monday September 14, 2026 whether it’s daylight saving time, or standard time or “they” decide on a fractional hour offset between the time you set the appointment and the time you attend the appointment. Past events: UTC timest…

Great advice! The really tricky part to me is when you have an event your recorded before it happened, but want to look it up after the event has passed (e.g., you want to look up the doctor's appointment a year after it occurred). The simplest and mostly solid answer I've been able to come up with is: 1. If you want to know when something happened and a particular place is important (like the previously mentioned do…

You have an appointment with the doc. That's just a placeholder for them to schedule employees for the day. You sign you when you arrive, UTC timestamp. They produce a record of your responses, UTC timestamp. Tests, UTC timestamps.

Your appointment time is no longer the important item once it's in the past. It's probably easier to just get a list of your visits from the last year, and select the one you want to dig into. Not listed? Expand the range to 13 months. I doubt a situation exists where it matters that one must query the exact date and time in order to find that appointment.

Re: British Columbia, Time Zones, and Postgres

#115

Post by [deleted] » Wed, Jun 24, 2026, 1:11 AM UTC

cozzyd wrote: post #104

mulmen wrote: post #96

Earlier quoted context omitted.

Specifically you need the TZDATA history of the local Postgres instance. Is the TZDATA version persisted at Postgres start time? Is it possible to query this information without recording it in the schema manually?

right, but in principle postgres could keep track of this as a feature.

[deleted]

Re: British Columbia, Time Zones, and Postgres

#116

Post by miki123211 » Wed, Jun 24, 2026, 3:51 AM UTC

eduction wrote: post #86

miki123211 wrote: post #80

Earlier quoted context omitted.

However, keep in mind that there is *no way* to store the time of a future event in a way that won't someday break unexpectedly. It just physically can't be done. Your approach assumes that we know what timezone the doctor's office will be in when the event happens. However, unless you know the exact lat/lon of that office — and maybe not even then — that's not something you can rely on. Countries sometimes split the…

Everything in the future is provisional and uncertain. The doctor could die, humanity could get obliterated, the database could go offline and you lose all your appointments. Should we all add precise GPS coordinates to our salon appointments in case that neighborhood is seized by commandos from Newfoundland who really really want us all on GMT? I’m personally not sure it’s worth the effort.

"The doctor's office no longer exists" is the fault of whoever bombed the office, and not a bug in your software. "My phone shows me 3PM and they call me at 2PM to see where I am" is a bug in your software, at least according to the understanding of most users. "Oh you haven't taken the 2028 US second Civil War into account" is not a valid explanation.

Re: British Columbia, Time Zones, and Postgres

#117

Post by Ndymium » Wed, Jun 24, 2026, 5:30 AM UTC

jagged-chisel wrote: post #106

Ndymium wrote: post #85

Earlier quoted context omitted.

Copying what I posted under the original[0] that no one noticed because it's quite relevant to your mention of UTC for past events: The naming of "timestamp with time zone" is one of my favorite pet peeves. It's one of those things that you can say "well technically it's true" about. The article suggests that for past events, UTC and this timestamptz would be acceptable as a general rule, but even there it depends on…

But if your user does jog from 2:00am to 3:30am, your localized data is going to look wrong in the fall in a timezone where the clock changes from daylight saving and the 2:00am hour repeats itself. It’s easier to convert historical records from UTC because the official rules don’t change for past dates (there may certainly be data errors in tzdata … what’re gonna do … )

If my user does jog at 2 AM, then it's better to show 2 AM than 7 PM or 10 AM even if there is a slight chance that on one singular day 2 AM might repeat itself. The user who is aware of how daylight savings works will surely be able to figure that out.

Technically you might argue that I should show 1 AM if the user did run at 2 AM in summer time, but everyone I personally know keeps their schedule over DST transitions. That is, if they did something at X o'clock before the transition, they'll keep doing it at X o'clock after it (sleep be damned). So generally showing 2 AM would be the most correct solution.

You cannot get that information from just UTC if you don't know where the user was when they made those historical events. Thus you either have to keep a history of their location (complicated) or just store the local timestamps (or at least the offsets) at the time of event. Always being able to convert from UTC with no extra data assumes that the user will never move, which might be fine if your application is limited to users in a single country.