On 05/27/2011 01:43 PM, Alvaro Herrera wrote:
Hi,
One of our customers is interested in being able to store original
timezone along with a certain timestamp.
I am very interested in the use-case for this (in part as I'm working on
a PG related time talk). My experience thus far is that people who want
this do not fully understand the nature of date-time calculations and
variables in PG.
It is currently possible to store a TZ in a separate column, but this is
a bit wasteful and not very convenient anyway.
Are there objections to the general idea? If not, I'll flesh a more
complete proposal.
I'm not crazy about it.
Although time-stamp-with-time-zone is, perhaps, a bad name for what is
actually a "point in time", a point-in-time is what timestamptz
represents. I can enter it and allow my defaults to take over, specify
abbreviations, explicit offsets or long names none of which change the
actual point in time. Likewise, I can display said point-in-time in any
of dozens of ways according to my needs.
steve=# select '2011-05-27 12:34'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07
steve=# select '2011-05-27 12:34-07'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07
steve=# select '2011-05-27 12:34 PDT'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07
steve=# select '2011-05-27 11:34 PST'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07
steve=# select '2011-05-27 15:34 US/Eastern'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07
select now() - '02:58:54.605041'::interval;
?column?
-------------------------------
2011-05-27 12:34:00.394959-07
Granted, I'm a random sample of 1, but I've never found anyone with a
real need for this feature - especially since the capability already
exists to achieve the requested result, and much more flexibly, by
either a separate column or a user-defined type.
Questions:
What would be the storage impact (tables, indexes and backups) for those
of use with tens-of-millions of pieces of timestamp data?
What type of timestamp would be stored? Abbreviated/offset (PST, -07),
full (US/Eastern) or a mix? Is there an expectation that the stored time
zone information would be used for any calculation purposes? If so, how
would rules be applied? Would there be any form of error-checking?
Currently PG accepts non-existent time zones but maps them to UTC:
steve=# select '2011-05-27 15:34'::timestamptz at time zone 'US/f00';
timezone
---------------------
2011-05-27 15:34:00
Would there be any impact to existing queries?
How would dump/restore issues be handled - especially if the time-zone
info changes in between?
More as I think of them.
Cheers,
Steve
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
