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

On 10/5/14, 5:42 PM, Tom Lane wrote:
> 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.

FWIW, I agree for timestamptz, but I do wish we had a timestamp datatype that stored the exact timezone in effect when the data was entered. That can really, REALLY save your rear if you screw up either timezone in postgresql.conf, or the server's timezone. The part that seems hard (at least to me) is the question of how to actually store the timezone, because I don't think storing the text string "America/Central" is going to cut it. :/
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com