Evan Siroky's Timezone Boundary Builder[0] is a good source of time zone boundary mappings. It can be used via a library in nearly any language you would want[1], or loaded directly into Postgres and used with PostGIS in queries[2] (of course, if you load it into your DB you should have a process for keeping it updated).

It would be wonderful if Jiff supported coordinates-to-tz lookups out of the box :)

[0]: https://github.com/evansiroky/timezone-boundary-builder/rele...

[1]: https://github.com/evansiroky/timezone-boundary-builder#look...

[2]: https://www.crunchydata.com/blog/timezone-transformation-usi...

▲

burntsushi 3 days ago | parent [-]

I'm aware, yet it doesn't invalidate my point. You'll note that I didn't say it was impossible to do. You would also need to rely on this database being updated similar to the tzdb, which is another data dependency to consider. There is also the problem that there isn't an established interchange format for such things, but there is one for IANA time zone identifiers (RFC 9557).

Finally, the database you linked doesn't appear to track historical data, unlike the tzdb. So if Ukraine reacquired territory, you'd end up with a wrong answer if looking at appointments scheduled in the past.

▲

akio 3 days ago | parent [-]

Past events should just be stored as instants, not as a local time plus location or time zone.

Further, I believe the historical data is meant to be handled by the named time zone, so Russia moving Dnipro to MSK would create a new time zone, call it Europe/Dnipro, then Ukraine reacquiring the territory and moving it back to EET would keep (or further split) Europe/Dnipro, and the historical changes of Dnipro would be handled by tzdb.

So even if you did store past events with a local time plus a location (which you should not do), I don't think Ukraine reacquiring the territory would cause problems, although we're really stretching my knowledge of IANA named zones and I could certainly be wrong.

▲

burntsushi 3 days ago | parent [-]

> Past events should just be stored as instants, not as a local time plus location or time zone.

Appointments become past events. Sounds like an operational nightmare to convert all of them right as they become past events.

And you absolutely want the time zone (and offset) if you want to show past event as a local time for where that event was held. You don't need the location because you know what time zone rules governed that location at that point in time in the past. It won't change.

Please consider that I phrased my comment in engineering terms. I already acknowledged that you want a physical location for the best results in all cases, as you have pointed out in this thread. But I also was careful to contextualize it in engineering terms: storing that information comes with extra engineering costs over just storing an RFC 9557 string. Sometimes those costs are worth it, but certainly not for every use case.

> Further, I believe the historical data is meant to be handled by the named time zone, so Russia moving Dnipro to MSK would create a new time zone, call it Europe/Dnipro, then Ukraine reacquiring the territory and moving it back to EET would keep (or further split) Europe/Dnipro, and the historical changes of Dnipro would be handled by tzdb.

I can believe that this is what would happen.

	▲	akio 3 days ago \| parent [-]
		> Appointments become past events. Sounds like an operational nightmare to convert all of them right as they become past events. That is fair. In some models there's a natural separation between a scheduled-at time and a happened-at time (e.g., in one I worked on scheduled_events was a separate table from past_events), but I accept that's certainly not all, or even most. I shouldn't have made such a blanket statement. I do believe the statement is good advice in cases where your data does not mix both future and past times. > Sometimes those costs are worth it, but certainly not for every use case. This is also fair. My experience with time zones has been the more correct I can make my time models, the better I sleep at night, but of course you're right engineering involves tradeoffs. I think it's important to at least know the most correct way so that you are making educated decisions in your tradeoffs. Also, FWIW I'm not the person you originally replied to, although the Ukraine example was mine.