Re: storing TZ along timestamps - Mailing 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?"