From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: storing TZ along timestamps |
Date: | 2011-05-27 23:13:12 |
Message-ID: | 4DE03008.5050900@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2011-05-27 23:29:55 | Re: storing TZ along timestamps |
Previous Message | Greg Stark | 2011-05-27 22:54:05 | Re: storing TZ along timestamps |