Re: storing TZ along timestamps

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