Join the discussion
Write your take first — we'll ask for email only when you're ready to publish.
- Hacker News
- > Going forward, the UTC offset for America/Vancouver timezone is permanently UTC-7.
A rather bold use of the word “permanently” given that the province just changed the previous permanent setup.
by eduction - As the author of this blog post, your comment is overwhelmingly my favorite.by winslett
- The issue here seems to be that the behavior of tzdata (correctly) changes over time. Can all this complexity be avoided by storing the tzdata version in the timestamp itself so it can decoded with the same rules?by mulmen
- It's just a different kind of complexity and one that requires having a library that can arbitrarily load different versions of tzdata.
I've been thinking about it for a while though - a time zone conversion library that also accepts an additional "tzdata_version" argument.
by dqv - This is one of those cases where I would prefer to be antifragile and rapidly "patch the data" once as opposed to trying to perfectly solve problems like this before they arise. In all likelihood this will never happen in a particular timezone.by pphysch
- > 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
2023d - changes to past and future timestamps
2023c - changes that changed future timestamps reverted
2023b - changes to future timestamps
I definitely prefer the... fragile? approach for this problem
by dqv - This problem isn't specific to timezones. In general, you did X on the basis that A was B, and now you know that that A is actually C. The strategies to handle that are quite interesting:by thisrod
- Lesser known is that western parts of British Columbia are still debating what timezone to adopt. E.g. the East Kootenays which used to align with Alberta's mountain time w/ daylight savings is now debating whether to align with B.C. or Alberta now that Alberta is also switching to permanent Mountain Daylight Time (which they call "Alberta Time").by kelseydh
- Western? Alberta is east of BC.by userbinator
- Time is local Timestamp is a counter
A unix timestamp does not have different timezones. It is a counter. No matter where u are in the world a timestamp call should give you the same numeric value at the same instant. It is not time zone adjusted. Store that number, unadjusted as the source of truth. You can get to any local time after that.
by necro - I would contend that you shouldn't store anything but current unix timestamps in UTC in your database. If you must store time in some other way, then the two column method in the post will work, but leave it up to your software library to do it.
I prefer to leave all the time conversions to software, wherein you only use battle tested libraries, and never do it by hand.
Timezones are just too fraught with peril to try and do it on your own.
Edit: changed some words to make clearer what I was saying.
by jedberg - If you don't understand what the library is doing, and blindly put in local time without any consideration, you will get bitten someday. And all libraries use the same timezone database/logic anyway and run into same issues the author describes.by rini17
- In that case only storing utc did not work when you created a date in the future before you updated tzdataby merb
- It‘s a common mistake to store everything as UTC timestamps and shows lack of understanding of time domain. Local time exists and it is neither UTC or timezone-dependent. Doctor office opens at 8 a.m. regardless of whether it is DST or not. Appointments are made in local time. Store them in local time.by ivan_gammel
- The issue described in the post is an example of when you cannot just rely on Unix timestamps. Specifically it comes down to which date is authoritative.
A appointment with your dentist at 2pm Pacific Time in December 2026 has changed Unix timestamps in British Columbia. The dentist doesn't care about the Unix timestamp, she cares about the wall clock local time when you arrive for the appointment.
by ppchain - ANSI SQL has DATE and TIME types. Just use them for appointments bound to location. Conversion to current user timezone must happen in presentation layer and certainly does not belong to a database.by ivan_gammel
- Whenever I see a tz post, I want to remind ya'll that the `tzdata` package is using data from `eggert/tz` by Paul Eggert -- a crazy good UCLA professor. I took his course once and in the exams, he'd like to put in a question he didn't know the answer to himself.by chaidhat
- An added wrinkle is that parts of British Columbia use other timezones.
The southeast corner follows Alberta time (previously MST/MDT but changing to MDT).
Parts of the northeast and iirc a few other communities (eg Creston) have historically followed MST (no switch) and will now be effectively on the same time as Vancouver, albeit probably with a different TZ designation(?).
by rjrjrjrj - The East Kootenays still hasn't decided what timezone to align with. They used to align with Alberta (w/ daylight savings switching) as well. Then B.C. announced their change and the East Kootenays announced they were aligning to Pacific Daylight Time, which meant for the East Kootenays only uniquely "falling back" an hour a final time this fall. Then the East Kootenays rescinded that decision, and since then, Alberta has announced permanent daylight time. Now they need to decide which to align with.
The current plan is for the East Kootenays (America/Cranbrook) to "fall back" to PDT this fall, but IANA TZ database still hasn't made the update because of the uncertainty.
IANA revert: https://github.com/eggert/tz/commit/afcea8761543ac97aeee6399...
https://www.cbc.ca/news/canada/british-columbia/what-time-is...
by kelseydh - This problem is not new and is a relatively minor exposure to the sort of issues that TZ conversion constantly needs to deal with. Different parts of the world have different dates that they adopt (or don't adopt) DST and some nations have changed this date in the past.
Use a library, do not roll it yourself, do not try to outsmart tzdata... if you think you could then please volunteer for this project and either become a new Chronomancer[1] or get disabused of that misconception.
1. It's a legal title, people actually have to call you a Chronomancer if you've contributed to tzdata, it's the law.
by munk-a - What happens if I booked an appointment before the rules changed and TZDATA could be updated and deployed?by mulmen
- Indeed, recognizing Chronomancy will once have soon always been the case.by Terr_
- 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 timestamp.
What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
Note: nothing stops you from optimizing for queries by adding a field to store (or using a calculated index for) the integer epoch offset (e.g. unix timestamps), just make sure you know which field is authoritative.
- UTC for past events doesn't always work either. For example, historical employee punch-in times.
UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
by Xirdus - Always store the location, too. Space-Time is a thing.by _glass
- What about virtual events between participants in different time zones? Whose do you keep stable if one has their clock moved under them?by lalaithion
- 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 doctor's appointment), store the local date/time with timezone data. That covers you in case the timezone changes before your recorded event happens. Personally, I would not store reflexively store dates/times in a string. For the cases I encounter, that feels like primitive obsession since you can always use EXTRACT in a query to simplify output.
2. If will you need to lookup the date and time after an event occurred, write a separate field that includes timezone offset field (e.g. -1, +1, -8, etc.) in case you want to look up exactly when an event previously happened. This (mostly) covers you from timezone shifts that occur at that particular location between when you wrote the data vs some later date. This falls apart if the timezone you're converting to also changed their timezone between now and the event. Also, if your timezone shifts between when you wrote the record and the actual event.
I wonder if someone has a temporal record of timezone shifts. You could solve a lot of edge cases with something like that. Then you could write a query that asks for the timezone's offset as of a specific date. That would make life much easier. Then you could skip the timezone offset field I mentioned in #2.
by infamia - > What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
You can just use a TIMESTAMP with no TZ data. It's functionally the same as using the string but simpler because you avoid all the string handling headaches and gain the benefit of avoiding to avoid double booking and date/time functions to answer questions like "how many appointments do I have in April?".
by mulmen - 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 themselves up. Provinces get annexed. Border towns may end up on the other side due to a treaty, even in times of peace. Multi-timezone countries may change which parts belong to which timezone. A town may get occupied, and the answer to the question of "what time is it" may depend on the loyalties of the person you ask.
Unless the doctor's office is physically located in Berlin, Germany, there is no guarantee that europe/berlin will always be its correct timezone. Even then, you may get the east/west Berlin split and one side deciding to abandon DST.
When an event happened in the past, we know exactly when it happened, and we can express that timestamp as "number of seconds after some reference point." When an event is planned for the future, we usually plan it for a specific hh:mm in a specific location, but we don't know when that is actually going to be.
by miki123211 - 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 what you will be doing with the data. If you intend to interpret it as a series of local occurrences and try to visualize/summarize that data later, you may be in for a surprise as your user has moved to another timezone and now all the past events are translated to the wrong local hours [1]. For example, your system might end up showing that the user's best time for jogging based on historical data is at 2 in the night.
by Ndymium