Re: storing TZ along timestamps

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?

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)?

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.

Cheers,
Steve