I’ve thought about the proposed solution before (spoiler: the author proposes to store the user’s intended date/time AND the TZ qualified date/time.)
The problem is that, except for UTC, any timestamp relating to a user’s intention must ALSO store a geolocation. Dinner at 6pm? Great! Where (on earth)?
And I’m not sure what to make of storing a TZ qualified timestamp AND a non-TZ qualified timestamp. I’m not sure what problem that solves or how to reconcile if they differ.
I’m still in the “store as UTC” and be very careful about disambiguating at creation time.
My recommendation is actually to store the local time and the exact location, if you can get it. Then use that location to determine the correct timezone.
Some users may not know that their Timezone is America/Los_Angeles but they should hopefully know where their venue is.
You always treat the local time - 6pm on 5th December in Boise, Idaho - as the core truth and derive the UTC version from that. Then if Idaho decide to cancel daylight savings time you can update your calculated and stored UTC timestamps to the correct values.
> use that location to determine the correct timezone
Does this mean consult an "address to timezone lookup service?" Not sure if they exist, and I don't see another way without user specifying manually. Maybe a string match, but it could be wrong/unsure somewhat often.
> Then if Idaho decide to cancel daylight savings time you can update
Looking up events within a time interval is easy enough, those in city (timezone?) limits not so much. Consult a GIS system, search within polygons??? String match or every city in your database?
It's possible these days to run an entirely open source geocoding stack of your own. Here's one of the better options for that: https://github.com/osm-search/Nominatim
I wouldn't recommend it though: it needs a beefy machine and applying updates on an ongoing basis is fiddly. Better to use a commercial option and have that as an escape hatch.
My recommendation is actually to store the local time and the exact location, if you can get it. Then use that location to determine the correct timezone.
Some users may not know that their Timezone is America/Los_Angeles but they should hopefully know where their venue is.
The location is implied by the time zone. The proposal is to store the user’s calendar date, wall-clock time, and time zone. (Technically, you also need a DST marker for the ambiguous hour in the fall when the clock is turned back and repeats 2–3 am.) From that you can derive a UTC timestamp.
So I’ve been wondering this for a while. Why don’t we actually use a „spacetime“ format, like 2024-12-05T23:03:47+48.86,2.34 instead of bothering with time zones at all? Why not use arbitrary precision coordinates in their place? That would solve a bunch of issues with time zones at once, like not needing to know which time offset existed at the point in time in question. Depending on the use case, we could make the coordinates more precise, but regularly, I’d wager one or two digits would suffice for current timestamp usage.
There's usually a need for a human to see/understand the timezone information - probably doesn't matter how you store it, but showing JUST the RAW spacetime format will annoy a certain group of users (the 99% who don't/can't map from lat/long or whatever format you choose to a more human-legible "hometown, country" format)
But then you'd still need to figure out in what timezone those coordinates are to know when it is/was, so you made the problem even more complex. Unless you're suggesting we get rid of timezones in general, in which case it would be the same time everywhere and we don't need anything for disambiguation, i.e. we can get rid of the coordinates.
Inb4 that other blog post that explains why getting rid of timezones is troublesome in everyday life.
It's more complex, but usually it better matches user intent. If I say the conference starts on 2027-04-05 13:09 in Berlin, and Germany decides to change their time zones or daylight saving time rules sometime in 2025, your stored offset or stored UTC time is now wrong. The conference will start whenever local clocks show 13:09, and you have no reliable way of knowing which time zone local clocks will be in in two years time.
Of course if we go down that path we should also add a format to indicate offsets. If I say I'll be back in 10 minutes, that interval shouldn't change no matter what happens to the time zone. To perfectly disambiguate it you would need to store it as current time (with timezone or UTC) plus the intended offset. This would also "solve" a lot of issues around leap seconds and the inability to predict them more than six months into the future.
The geo lookup would be expensive computationally, and also there are arguments over geographic boundaries that could make TZ lookup ambiguous (the author gave an example in the article)
> The geo lookup would be expensive computationally
Not really? There's not that many timezones, globally, and I think their polygon data is in OSM / could be obtained from OSM. Simply brute-force colliding a single point with all of them I would expect to be basically "instantaneous", but there are a number of trivial optimizations, like bounding boxes, that you could do to speed it up.
(Point-in-polygon is not terribly expensive; it's O(segments) in your polygon.)
That said, there are problems with that, as you mention. I just don't think "computationally expensive" is one of them.
The problem is that, except for UTC, any timestamp relating to a user’s intention must ALSO store a geolocation. Dinner at 6pm? Great! Where (on earth)?
And I’m not sure what to make of storing a TZ qualified timestamp AND a non-TZ qualified timestamp. I’m not sure what problem that solves or how to reconcile if they differ.
I’m still in the “store as UTC” and be very careful about disambiguating at creation time.