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.
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.
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 … )
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.
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.
Funnily enough, Google Maps' Timeline tells me I went to a different restaurant 5 years ago. Imagine if you went to a McDonald's in 2019 and in 2021 it became a KFC. Google Maps today will say "You visited this KFC in 2019"!.
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.
I'd say an event should have the date/time in local offset AND the location details rough or fine grained. OR the date/time should be stored/transmitted in UTC if it doesn't have a localized date+time+location.
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.
If you're feeling nice, randomize whose time remains stable (to keep things fair), keep the organizer's time stable, or pick the time that minimizes the number of participants who will have the meeting time change.
If you're feeling mean, randomize whose time remains stable (to make it hard to predict), move the meeting for the organizer, pick the time that maximizes the number of participants who will have the meeting time change, or split the difference and move everyone. Meeting was at 10 AM for Alice and 9 AM for Bob, but now it can be at either 11 AM for Alice and 9 AM for Bob or 10 AM for Alice and 8 AM for Bob? Now the meeting is at 10:30 AM for Alice and 8:30 AM for Bob.
Every event should have an IANA timezone tied to a city like America/Vancouver or Europe/Berlin, and it should ideally be settable by the user. Some apps (e.g. Discord) don't expose this but have a time zone under the hood, and it's a huge pain every time daylight savings time comes along when an event's time zone is incorrectly in Europe instead of North America
> 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?".
I am willing to concede to “human readable” and dropping “string” iff queries on TIMESTAMP are producing a human readable string (I believe they are … I haven’t been in postgres in at least six weeks and details like that don’t make a lasting impression in muh brain)
Why would queries on TIMESTAMP not produce human readable strings? Postgres has defaulted to a human readable mask for timestamp presentation for at least 25 years.
Why not? It sounds like it would be correct even if the employee has a shift that includes a offset change.
Future timestamps should be local because local timezone changes literally change the instant the event it will happen (relative to UTC). For past things, this can’t happen
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.
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 America/Sao_Paulo.
edit:
[1] unless it was wrong. in which case, you actually still want the UTC timestamp stored, so that you can just update tzdata and get correct local times, as opposed to saving the wrong local times in your database, that you now have to also fix.
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 normalize 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.
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 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).
Sometimes, the relevant timezone is whatever the local timezone was when and where the timestamp was recorded. Which is information that's lost on UTC conversion.
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.
Not always a math bug. Sometimes a human bug. Tzdata can have errors (it's crowdsourced after all) that cause past UTC stamps to be incorrect because that incorrect tzdata was used at conversion time. And since most people aren't storing the tzdata version they're using with the stamp, it would be very difficult to make corrections without also corrupting other stamps.
The bottom line is, if wall time is important, past or present, wall time needs to be stored.
The only thing that can be guaranteed about a UTC timestamp is it's a UTC timestamp.
Changes to past and future timestamps
Since 2022 Moldova has observed EU transition times, that is, it has sprung forward at 03:00, not 02:00, and has fallen back at 04:00, not 03:00. (Thanks to Heitor David Pinto.)
Interesting. I could see that as an argument also for storing it in UTC, no?
For example, if tzdata is 1 hour off, and you store your timestamps in UTC, it's immediately obvious that a local time is wrong because users will see events that just happened as having happened 1 hour ago. Update tzdata, and now everything is right.
If you store the wall time, it _looks_ right, but fails if you attempt to compare/sort it with times in different timezones. To fix it, you need to actually modify the data in your database.
Like the time clock example: sure what you're describing works if the user is just pressing a button to clock in and the server stores a UTC timestamp in response to a POST request or whatever.
But it's very common to need to backfill time. So the user backfills with their own supplied timestamps, those stamps get converted to UTC, tzdata changes a few months later, and HR is now asking for an explanation as to why they were late for those backfills and how it's possible they were working an hour after the shop closed.
It's never as simple as "just store it in UTC".
Conversion to UTC is lossy, so I prefer to keep up with the user-supplied time where appropriate.
"UTC or otherwise" is important. Are you storing otherwise, or are you storing UTC? There are times where storing otherwise will lead to data loss in case the law changes whereas storing UTC would work (when you care about a literal point in time, like access logs). And there are times where storing UTC will lead to data loss in case the law changes whereas storing otherwise would work (when you care about wall clock time, like punch-in times).
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(?).
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.
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.
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.
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.
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.
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.
To frame it another way, your dentist has agreed to a particular triggering condition. (Whether they realize that's how their country works or not.)
We can all make pretty-good guesses about when that condition will be fulfilled in N seconds, but until it actually finally happens, the true occasion could land somewhere else.
"Three months later at 2PM where I live" is not so different than "when the thrush knocks during the setting light of Durin's Day." You can guess that it's N seconds from now, but you might be wrong.
I edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
I don't understand this. The consumer books in his/ her local time stamp i.e. 12 PM pacific. Gets stored as Epoch milliseconds (and is passed around as a data structure i.e. Date struct with UTC as the timezone) and the providers sees the time stamp 3 PM EST or 2 PM CST depending on it's timezone at runtime (interface the provider it works with).
I don't understand why a specific timezone has to be "authoritative" here. What am I missing.
Clocks are social constructs, and your system needs to have some decision in it about whose clocks take precedence over other ones.
No matter which of these you choose, there's a possibility that someone says: "Hey! That's wrong! I never agreed to that":
1. The meeting shall be in precisely N seconds, no exceptions. (UTC, or something very close to it.)
2. The meeting shall be when participant A's wall-clock shows X.
3. The meeting shall be when participant B's wall-clock shows Y.
At the present instant, you might have predicted values so that X Y and N all land on the same spot, but the prediction is not reliable and the equality will collapse if anybody's government makes timezone changes. Or if their country is taken over by another. Or splits due to civil war.
The appointment is for 12/1/26 2PM of whatever timezone the office is in at the time of the appointment.
Between the time of booking and the time of the appointment, the government changed what timezone there will be at the time of the appointment. If you calculated the Unix timestamp at the time of booking using the old laws, by the time of the appointment the Unix timestamp would be off by an hour.
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).
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.
You have to choose one timezone to be authoritative here, which one you choose depends on the situation, but if you don't choose one, then a change like the one happening in BC will cause the consumer and provider to be using mismatched times.
If the consumer booked something for 12PM Dec 1 2026 PST, and this booking was made before BC's changes were announced, and the provider saved this using their local time (say EST), then they would have saved it as 3PM EST. But now with BC's changes, when the consumer shows up at 12PM their time, that will be 2PM EST, an hour before the provider was expecting.
Was the consumer correct for showing up at the booked time according to their wall clock, or is the provider correct with their saved the time that was an hour later? The answer probably depends, but whichever you choose is the authoritative time.
you booked an appointment in the future. before the day of your appointment arrives, british columbia changes the timezone. all appointments after that change now must follow the new timezone rules. therefore the time of your appointment relative to UTC is now different.
Except that the timezone in British Columbia has now changed. Lets say you stored the appointment for November in UTC.
So you set the time to 10pm UTC to match 2pm British Columbia. But because the timezone for BC has changed that 10pm now matches 3pm in British Columbia
So the Dentist is expecting you at 2pm and you come along at 3pm
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.
> Appointments are made in local time. Store them in local time.
You may just be illustrating a particular use case, but it is more ambiguous in the general case. For example if you have arranged a meeting with someone in another timezone then maintaing the local timezone could lead to a misalignment for one of the participants.
This is ok. When future local user time offset is unpredictable, pinning meeting time to a certain location is a good strategy. If at some point meeting shifts for some user, well-designed calendar app could warn them and let reschedule.
It still works better than pure UTC because it is predictable for at least some of the users.
I edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
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.
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.
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").
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:
I encountered it when I was design the scheduling back-office for a LED video wall a few years ago when those became economical for a shop to own and run 24/7.
The customer probably never noticed if I even did it “correctly” or couldn't be bothered if I didn't, but I remember I was bothered by it: (1) ensuring continuity of programming during the gap when it jumps forward (2) solving the ambiguity when it went backwards.
Because obviously they wanted to think in local time.
You're right that for the most part this is avoided by convention and scheduling time changes at quiet times of day.
A bit contrived but consider you are a maintenance worker in a facility that uses isolated timekeeping devices. "Change the clock on the vault back one hour at 3:00am".
Sounds like the quoted RFC would help here. Storing the offset would make it unambiguous which of the two moments was meant. Your business logic would have to figure out what to do when the offset no longer exists (honour the clock time or convert to the new timezone) or is nonsense. The geographical reference would help decide what to do if you're not in a single location.
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.
Yeah; you can see some of the rationale here - https://savestandardtime.com/ (not affiliated, and they're a little wacky about it, but I broadly agree) - but the best argument is it seems to be better for sleep.
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".
And then in a bunch of press conferences and answers to the public, they would say "oh, this is what people chose" from the poll.
If we did that the sun would be coming up at 4am right now in Revelstoke. What's the point of the sun being up at 4am?
On the other hand, I don't like it getting dark at 3:30 in the afternoon in Vancouver around Christmas. I know it means it will be darker later in the morning but you wake up in the dark that time of year already anyways.
Go for a run, hit the ski hill, all before you start work. The sun runs your health (circadian rhythm). I'm in Vancouver and this is the dumbest change. We're going to pay for this for a decade in premature deaths, and we'll end up on standard time anyway.
I always get up with the light so I think 4am would be too unreasonable. Especially if most cafes remain closed for hours still. 5am is tolerable.
As for winter, an extra hour of dark doesn't feel like a huge deal. When I commuted in the winter it was often happening around 6am while still dark anyways.
I have a feeling people's morning habits will change a bit to compensate for the darker mornings though. So I think it'll work out in the end.
Where do you expect the premature deaths to come from? Rush hour traffic in the dark?
That reminds me about a nitpicky argument around Brexit, they were set to leave at midnight February 1, 2020, but... in which timezone? Brussels time, or UK time?
The EU won and the UK left at 2020-01-31, 23:00 GMT (local time)...
BC resident. I do look forward to not adjusting to a twice-annual time change. BUT, I would have strongly preferred sticking to Standard Time, year round, instead of DST. That option was apparently not on the table, and strangely got little mention or investigation by media.
So a daily event approximating High Noon is literally gone forever here. Yes yes, depending on longitude, it was never there, but now it's even farther away. Life goes on...
I hope this gives us Americans the needed encouragement to do the same on the west US coast. Utter insanity to screw with the clocks twice a year instead of letting various institutions who have a compelling need, to publish "Summer hours" to suit them.
Apparently there is a Federal restriction: we can opt to eliminate DST, like Hawaii and Arizona, but we cannot unilaterally decide to adopt "permanent DST" as did British Columbia. So we'll have to figure out how to get both houses of Congress to pass a bill into law, and have the President sign it, without said institutions convincing the world that keeping your clocks 1 hour forward is woke propaganda and injects gay chemicals into tadpoles.
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.
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.
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?
Yeah that's exactly what I am thinking. It could be a wrapper. But I think it would be better to take the (UTC) timestamp of the time of insertion instead of the TZDATA version itself. Then the Postgres instance can handle what version of TZDATA it had at that time.
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?
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.
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.
[0] https://news.ycombinator.com/item?id=48558005
[1] https://blog.nytsoi.net/2022/03/13/utc/
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 … )
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.
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.
Funnily enough, Google Maps' Timeline tells me I went to a different restaurant 5 years ago. Imagine if you went to a McDonald's in 2019 and in 2021 it became a KFC. Google Maps today will say "You visited this KFC in 2019"!.
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.
I'd say an event should have the date/time in local offset AND the location details rough or fine grained. OR the date/time should be stored/transmitted in UTC if it doesn't have a localized date+time+location.
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.
What about virtual events between participants in different time zones? Whose do you keep stable if one has their clock moved under them?
If you're feeling nice, randomize whose time remains stable (to keep things fair), keep the organizer's time stable, or pick the time that minimizes the number of participants who will have the meeting time change.
If you're feeling mean, randomize whose time remains stable (to make it hard to predict), move the meeting for the organizer, pick the time that maximizes the number of participants who will have the meeting time change, or split the difference and move everyone. Meeting was at 10 AM for Alice and 9 AM for Bob, but now it can be at either 11 AM for Alice and 9 AM for Bob or 10 AM for Alice and 8 AM for Bob? Now the meeting is at 10:30 AM for Alice and 8:30 AM for Bob.
This is the critical problem with all of this.
Daylight savings time changes, can't be globally banned fast enough really.
Every event should have an IANA timezone tied to a city like America/Vancouver or Europe/Berlin, and it should ideally be settable by the user. Some apps (e.g. Discord) don't expose this but have a time zone under the hood, and it's a huge pain every time daylight savings time comes along when an event's time zone is incorrectly in Europe instead of North America
Always store the location, too. Space-Time is a thing.
> 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?".
I am willing to concede to “human readable” and dropping “string” iff queries on TIMESTAMP are producing a human readable string (I believe they are … I haven’t been in postgres in at least six weeks and details like that don’t make a lasting impression in muh brain)
Why would queries on TIMESTAMP not produce human readable strings? Postgres has defaulted to a human readable mask for timestamp presentation for at least 25 years.
See Current: https://www.postgresql.org/docs/current/datatype-datetime.ht...
And 7.1 (2001-04-13): https://www.postgresql.org/docs/7.1/datatype-datetime.html (Section 3.4.2)
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.
Why not? It sounds like it would be correct even if the employee has a shift that includes a offset change.
Future timestamps should be local because local timezone changes literally change the instant the event it will happen (relative to UTC). For past things, this can’t happen
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 America/Sao_Paulo.
edit: [1] unless it was wrong. in which case, you actually still want the UTC timestamp stored, so that you can just update tzdata and get correct local times, as opposed to saving the wrong local times in your database, that you now have to also fix.
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 normalize 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.
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 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.
Sometimes, the relevant timezone is whatever the local timezone was when and where the timestamp was recorded. Which is information that's lost on UTC conversion.
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.
If past timestamps (UTC or otherwise) are unreliable, then there is some kind of math-bug going on.
Not always a math bug. Sometimes a human bug. Tzdata can have errors (it's crowdsourced after all) that cause past UTC stamps to be incorrect because that incorrect tzdata was used at conversion time. And since most people aren't storing the tzdata version they're using with the stamp, it would be very difficult to make corrections without also corrupting other stamps.
The bottom line is, if wall time is important, past or present, wall time needs to be stored.
The only thing that can be guaranteed about a UTC timestamp is it's a UTC timestamp.
When was the last time tzdata was wrong about a period that already passed?
Most of my career I’ve seen problems where it’s out of date, never where it’s up to date and wrong.
For the 2026a release:
Interesting. I could see that as an argument also for storing it in UTC, no?
For example, if tzdata is 1 hour off, and you store your timestamps in UTC, it's immediately obvious that a local time is wrong because users will see events that just happened as having happened 1 hour ago. Update tzdata, and now everything is right.
If you store the wall time, it _looks_ right, but fails if you attempt to compare/sort it with times in different timezones. To fix it, you need to actually modify the data in your database.
Only for server-supplied timestamps.
Like the time clock example: sure what you're describing works if the user is just pressing a button to clock in and the server stores a UTC timestamp in response to a POST request or whatever.
But it's very common to need to backfill time. So the user backfills with their own supplied timestamps, those stamps get converted to UTC, tzdata changes a few months later, and HR is now asking for an explanation as to why they were late for those backfills and how it's possible they were working an hour after the shop closed.
It's never as simple as "just store it in UTC".
Conversion to UTC is lossy, so I prefer to keep up with the user-supplied time where appropriate.
Seems like for airtightness you'd store utc alongside utc of when timestamp was stored alongside timezone
"UTC or otherwise" is important. Are you storing otherwise, or are you storing UTC? There are times where storing otherwise will lead to data loss in case the law changes whereas storing UTC would work (when you care about a literal point in time, like access logs). And there are times where storing UTC will lead to data loss in case the law changes whereas storing otherwise would work (when you care about wall clock time, like punch-in times).
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(?).
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...
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.
Indeed, recognizing Chronomancy will once have soon always been the case.
What happens if I booked an appointment before the rules changed and TZDATA could be updated and deployed?
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.
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.
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.
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.
To frame it another way, your dentist has agreed to a particular triggering condition. (Whether they realize that's how their country works or not.)
We can all make pretty-good guesses about when that condition will be fulfilled in N seconds, but until it actually finally happens, the true occasion could land somewhere else.
"Three months later at 2PM where I live" is not so different than "when the thrush knocks during the setting light of Durin's Day." You can guess that it's N seconds from now, but you might be wrong.
I edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
The post is for the otherworldly magician who wrote your library then.
How does your system know when it is 2pm in British Columbia?
"Which date is authoritative".
I don't understand this. The consumer books in his/ her local time stamp i.e. 12 PM pacific. Gets stored as Epoch milliseconds (and is passed around as a data structure i.e. Date struct with UTC as the timezone) and the providers sees the time stamp 3 PM EST or 2 PM CST depending on it's timezone at runtime (interface the provider it works with).
I don't understand why a specific timezone has to be "authoritative" here. What am I missing.
Clocks are social constructs, and your system needs to have some decision in it about whose clocks take precedence over other ones.
No matter which of these you choose, there's a possibility that someone says: "Hey! That's wrong! I never agreed to that":
1. The meeting shall be in precisely N seconds, no exceptions. (UTC, or something very close to it.)
2. The meeting shall be when participant A's wall-clock shows X.
3. The meeting shall be when participant B's wall-clock shows Y.
At the present instant, you might have predicted values so that X Y and N all land on the same spot, but the prediction is not reliable and the equality will collapse if anybody's government makes timezone changes. Or if their country is taken over by another. Or splits due to civil war.
The appointment is for 12/1/26 2PM of whatever timezone the office is in at the time of the appointment.
Between the time of booking and the time of the appointment, the government changed what timezone there will be at the time of the appointment. If you calculated the Unix timestamp at the time of booking using the old laws, by the time of the appointment the Unix timestamp would be off by an hour.
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.
You're suggesting pre-filling future data based on extrapolating current rules (up to an arbitratily unknown high date) instead of using a constant.
I don't really see how that is a good approach in any capacity.
You have to choose one timezone to be authoritative here, which one you choose depends on the situation, but if you don't choose one, then a change like the one happening in BC will cause the consumer and provider to be using mismatched times.
If the consumer booked something for 12PM Dec 1 2026 PST, and this booking was made before BC's changes were announced, and the provider saved this using their local time (say EST), then they would have saved it as 3PM EST. But now with BC's changes, when the consumer shows up at 12PM their time, that will be 2PM EST, an hour before the provider was expecting.
Was the consumer correct for showing up at the booked time according to their wall clock, or is the provider correct with their saved the time that was an hour later? The answer probably depends, but whichever you choose is the authoritative time.
you booked an appointment in the future. before the day of your appointment arrives, british columbia changes the timezone. all appointments after that change now must follow the new timezone rules. therefore the time of your appointment relative to UTC is now different.
Except that the timezone in British Columbia has now changed. Lets say you stored the appointment for November in UTC.
So you set the time to 10pm UTC to match 2pm British Columbia. But because the timezone for BC has changed that 10pm now matches 3pm in British Columbia
So the Dentist is expecting you at 2pm and you come along at 3pm
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.
> Appointments are made in local time. Store them in local time.
You may just be illustrating a particular use case, but it is more ambiguous in the general case. For example if you have arranged a meeting with someone in another timezone then maintaing the local timezone could lead to a misalignment for one of the participants.
This is ok. When future local user time offset is unpredictable, pinning meeting time to a certain location is a good strategy. If at some point meeting shifts for some user, well-designed calendar app could warn them and let reschedule. It still works better than pure UTC because it is predictable for at least some of the users.
In that case only storing utc did not work when you created a date in the future before you updated tzdata
I edited my comment to make it clearer. I meant you should only directly store current timestamps, anything else you should leave up to a library to store as it sees fit.
How would this solve the British Columbia issue as described in the article?
Your time library is not going to design your database schema for you.
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.
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.
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.
Oops my bad, HN won't let me edit the comment. Yes I meant eastern parts of British Columbia.
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:
https://martinfowler.com/articles/bitemporal-history.html
This strategy fails for appointments during that hour where the clock goes back: they are ambiguous, can refer to two different moments in time.
That caveat aside: good.
I have yet to find a technological solution to this social problem.
Also, I have yet to encounter this problem. For personal events, I sleep during this time. For company events, we always avoid this time.
I encountered it when I was design the scheduling back-office for a LED video wall a few years ago when those became economical for a shop to own and run 24/7.
The customer probably never noticed if I even did it “correctly” or couldn't be bothered if I didn't, but I remember I was bothered by it: (1) ensuring continuity of programming during the gap when it jumps forward (2) solving the ambiguity when it went backwards.
Because obviously they wanted to think in local time.
I have, in the context of time series charts. Lots of back and forth with QA.
You're right that for the most part this is avoided by convention and scheduling time changes at quiet times of day.
A bit contrived but consider you are a maintenance worker in a facility that uses isolated timekeeping devices. "Change the clock on the vault back one hour at 3:00am".
Sounds like the quoted RFC would help here. Storing the offset would make it unambiguous which of the two moments was meant. Your business logic would have to figure out what to do when the offset no longer exists (honour the clock time or convert to the new timezone) or is nonsense. The geographical reference would help decide what to do if you're not in a single location.
Humans would often fail such appointments too.
I just need to know what happens to our 9am standups in Vancouver when the other team is in SF. If I'm doing the math correctly it moves to 10am.
Also, I've often picked a random city in Pacific time when setting timezones on hosts, so I guess it's going to cause me some headaches in the fall.
Don't worry, WA, OR and CA probably aren't far behind.
Dumb change on the part of British Columbia.
Source: me, BC resident.
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.
Ah, you mean picking pst as the permanent timezone?
Yeah; you can see some of the rationale here - https://savestandardtime.com/ (not affiliated, and they're a little wacky about it, but I broadly agree) - but the best argument is it seems to be better for sleep.
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".
And then in a bunch of press conferences and answers to the public, they would say "oh, this is what people chose" from the poll.
Correct and complete explanation.
If we did that the sun would be coming up at 4am right now in Revelstoke. What's the point of the sun being up at 4am?
On the other hand, I don't like it getting dark at 3:30 in the afternoon in Vancouver around Christmas. I know it means it will be darker later in the morning but you wake up in the dark that time of year already anyways.
Go for a run, hit the ski hill, all before you start work. The sun runs your health (circadian rhythm). I'm in Vancouver and this is the dumbest change. We're going to pay for this for a decade in premature deaths, and we'll end up on standard time anyway.
I always get up with the light so I think 4am would be too unreasonable. Especially if most cafes remain closed for hours still. 5am is tolerable.
As for winter, an extra hour of dark doesn't feel like a huge deal. When I commuted in the winter it was often happening around 6am while still dark anyways.
I have a feeling people's morning habits will change a bit to compensate for the darker mornings though. So I think it'll work out in the end.
Where do you expect the premature deaths to come from? Rush hour traffic in the dark?
Sounds like Brexit #2
That reminds me about a nitpicky argument around Brexit, they were set to leave at midnight February 1, 2020, but... in which timezone? Brussels time, or UK time?
The EU won and the UK left at 2020-01-31, 23:00 GMT (local time)...
Thankfully much smaller potatoes, but yah, I'm annoyed by it
BC resident. I do look forward to not adjusting to a twice-annual time change. BUT, I would have strongly preferred sticking to Standard Time, year round, instead of DST. That option was apparently not on the table, and strangely got little mention or investigation by media.
So a daily event approximating High Noon is literally gone forever here. Yes yes, depending on longitude, it was never there, but now it's even farther away. Life goes on...
Also from BC: we get up in the dark in the winter either way. May as well have it light when I get off work.
I hope this gives us Americans the needed encouragement to do the same on the west US coast. Utter insanity to screw with the clocks twice a year instead of letting various institutions who have a compelling need, to publish "Summer hours" to suit them.
Apparently there is a Federal restriction: we can opt to eliminate DST, like Hawaii and Arizona, but we cannot unilaterally decide to adopt "permanent DST" as did British Columbia. So we'll have to figure out how to get both houses of Congress to pass a bill into law, and have the President sign it, without said institutions convincing the world that keeping your clocks 1 hour forward is woke propaganda and injects gay chemicals into tadpoles.
Isn't the simple option to just move time zones? If you're not allowed to swap to PDT year round, just change to MST instead.
> 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.
As the author of this blog post, your comment is overwhelmingly my favorite.
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
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
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"
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?
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.
Yeah that's exactly what I am thinking. It could be a wrapper. But I think it would be better to take the (UTC) timestamp of the time of insertion instead of the TZDATA version itself. Then the Postgres instance can handle what version of TZDATA it had at that time.
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?
right, but in principle postgres could keep track of this as a feature.