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?
Comments
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.