Re: storing TZ along timestamps - Mailing list pgsql-hackers
From | Christopher Browne |
---|---|
Subject | Re: storing TZ along timestamps |
Date | |
Msg-id | BANLkTikx9OyLO4q390y-ungVuse0tuSytA@mail.gmail.com Whole thread Raw |
In response to | Re: storing TZ along timestamps (Steve Crawford <scrawford@pinpointresearch.com>) |
List | pgsql-hackers |
On Thu, Jun 2, 2011 at 6:06 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > On 06/01/2011 05:18 PM, Alvaro Herrera wrote: >> >> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011: >>> >>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: >>>> >>>> Hi, >>>> >>>> One of our customers is interested in being able to store original >>>> timezone along with a certain timestamp. >>> >>> I assume that you're talking about a new data type, not augmenting the >>> current types, correct? >> >> Yes >> > That eliminates many of my issues - I just didn't want the type changed > underneath me. But some considerations remain - including some new that have > crossed my mind: > > 1. How would the time-zone be defined in this composite? Offset from GMT? > Timezone (well, link thereto) with all DST rules intact? Would "extract" > need to be modified to include the ability to grab the timezone? That doesn't seem appropriate, because timezones are not always represented by strict offsets from GMT. Some frequently-used timezones represent variable offsets. ("EDT/EST", I'm looking at you!) > 2. What would be the precedence for defining originating timezone? Default? > Set timezone to? ...at time zone...? Based on the timestamp (2011-06-02 > 12:34:56-07)? > > 3. Would indexing/sorting include the originating zone? If so, how would > time zones collate (base offset, actual offset based on the timestamp, > name)? Some timezones contain discontinuities, so that the notion of sorting them seems implausible, as there isn't properly an "ordering." > 4. What would be the corresponding type when used with Perl/PHP/Python/... > applications - would they require special non-standard handling? > > Since this isn't going to alter my current beloved timestamptz and I don't > have a use-case I leave the decisions on the above to others. But in my > imagined use-cases I still see the originating zone as a separate piece of > information better handled as a different column - for example sorting by > timestamp plus priority or selecting everything for a specific time zone. I'd tend to think that this is best captured by having two pieces of information: a) The timestamp in UTC terms, so that it's a totally stable value, which is amenable to comparison against other timestamps (irrespective of timezone) b) A symbolic representation of the timezone, perhaps its name. It's not at all obvious that these ought to be treated as a singular data type. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
pgsql-hackers by date: