71–80 of 117 posts
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…
Earlier quoted context omitted.
Correct according to what? An employee who punched in at 9AM wouldn't show up as having punched in at 9AM anymore. Not unless you also store the exact timezone the UTC timestamps have been created with - but that's basically local timestamps with extra steps.
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…
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.
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
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…
What about virtual events between participants in different time zones? Whose do you keep stable if one has their clock moved under them?
Earlier quoted context omitted.
Why?
No the OP, but I'm also in BC and dislike this change. The change was made (partly) based on a bullshit poll of residents, that asked basically "Hey which would you rather do, use year-round Daylight Savings Time or keep switching every six months?" - notably not including the option a lot of people wanted (and which is well-supported by a lot of research as the best option), which was "use year-round Standard Time".…
Dumb change on the part of British Columbia. Source: me, BC resident.
^1: https://news.gov.bc.ca/releases/2019PREM0103-001748#:~:text=...
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?
If you want to see if an employee is late compared to the time their shift starts, the system needs to know the time their shift starts in UTC, because otherwise if they start a shift in during a timezone change, it’ll think they’re extremely late/early.
If you want to pay them for their (clock out - clock in) time, UTC.
If they’re a remote worker on your team, and you want see your entire team’s availability, you should probably see it in _your_ local time instead.
I’ve found that whenever it looks like I need to know the local time that the user had when they did something, it’s because I’m implicitly anchoring it to some other timestamp that my system doesn’t know and that should also be recorded in UTC (like, in this example, the time their shift is supposed to start).
Some nuance applies, of course.
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…
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.
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…
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.