101–110 of 117 posts
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").
Western? Alberta is east of BC.
Earlier quoted context omitted.
That law, voted years ago, has overwhelming public support (including mine). Source: an actual source^1 ^1: https://news.gov.bc.ca/releases/2019PREM0103-001748#:~:text=...
This is exactly what I mention in a sibling comment - an incredibly misleading survey is quoted here to make it look like there was overwhelming support. >>>More than 93% of the record 223,273 British Columbians who completed the Province’s survey on time observance have indicated they would prefer a move to permanent daylight saving time (DST). Sticking with Standard Time was not an option listed in that survey.
Earlier quoted context omitted.
Never heard of DST? The authoritative time is constant in the local time zone, but needs to change in UTC twice a year. This is the exact reason people store time in local time zones. Also remember the date/time where DST switching occurs is entirely timezone-specific, and it's not necessarily the same pattern every year (as demonstrated with British Columbia).
DST works fine with Unix timestamps. It's the scheduling changes that disrupt DST switch (or something else) need adjusting to. But this is usually planned in advance and everyone would (or at least should...) update their tzdata. The amount of issues you'll have due to those (comparatively rare) changes cannot even begin to compare to the amount issues you'll have with datetime stored in timezones.
I don't really see how that is a good approach in any capacity.
Earlier quoted context omitted.
Indeed, all you need to handle this correctly is the insertion (or update) time and the historical tzdata database. In almost all schemas, you will have this...
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?
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.
> 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…
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…
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…
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 … )
Earlier quoted context omitted.
This is exactly what I mention in a sibling comment - an incredibly misleading survey is quoted here to make it look like there was overwhelming support. >>>More than 93% of the record 223,273 British Columbians who completed the Province’s survey on time observance have indicated they would prefer a move to permanent daylight saving time (DST). Sticking with Standard Time was not an option listed in that survey.
Ah, you mean picking pst as the permanent timezone?
> 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.
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…
DateTime logic in general is just difficult in general.. Most systems can translate to/from UTC with ease, it's just when the localized offset of the futre changed like in this case.
Earlier quoted context omitted.
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?
Whichever timezone is relevant to the analysis later when the data is read. 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 w…
Sure, you can reconstruct it if you stored the "where" part somewhere else, and associated the "where" with the timestamp's timezone (e.g. the Offices table has a Timezone column that you JOIN with the punch-in times). But that assumes you stored it somewhere else. It also assumes said storage is readily available. It also assumes there was no human error in recording that value (what if the office was accidentally assigned Seattle PST instead of BC PST back when it didn't matter?) It also assumes it was possible to record it correctly in the first place (what if the office is in Kimberley, BC but the software only allowed selecting BC timezone?)
Alternatively, you store the timezone directly in the timestamp itself and avoid all these problems and more.