Re: storing TZ along timestamps - Mailing list pgsql-hackers

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