Re: Proposal for better support of time-varying timezone abbreviations

Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> writes:
> The use of an /as_at_date/ is far more problematic. The idea relates to
> how existing date/times should be treated with respect to the date/time
> that a pg database is updated with new time zone data files. In the
> simplest form: there would be a function in pg that would return the
> date/time a new time zone data file was entered into the system, so that
> application software can manually correct when the stored GMT date/time
> was stored incorrectly because the wring GMT offset was used due to the
> updated time zone data files not being in place. Alternatively, pg
> could offer to do the correction in a one-off action at the time the new
> zone data files were updated.

Right now there's basically no way to do something like that, since what
we store for timestamptz is just a UTC time instant, with no record of
what GMT offset was involved much less exactly how the offset was
specified in the input. We'd probably have to (at least) double the
on-disk size of timestamptz values to record that ... which seems like a
mighty high price to pay to fix a corner case. Not to mention that
nobody's going to be willing to break on-disk compatibility of timestamptz
for this.

In any case, my proposal is just about being able to correctly interpret
historical timezone abbreviations during input, not about changing what
we store as datetime values.

regards, tom lane