On 10/5/14, 5:42 PM, Tom Lane wrote:
Gavin Flower <gavinflo...@archidevsys.co.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
