Re: storing TZ along timestamps

On Thu, 2011-06-02 at 18:46 +0000, Christopher Browne wrote:
> > 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!)

In conjunction with a specific timestamp, a timezone does strictly map
to a single offset.

That is, unless it's a timestamp in the future, and someone decides to
adjust a timezone before the timestamp actually occurs. But that's a
problem with the current timestamptz implementation anyway...

> > 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 have a similar inclination. ">" seems like the fundamental operation
you'd want to perform on any timestamp (perhaps more so than equality),
and that's not well-defined if there is no total order (but several
meaningful partial orders).

However, I do see some nice benefits, too. The main one is that you can
easily get either local time or GMT out of it. So you can answer queries
such as "which of these activities occurred outside of normal business
hours" as well as "which of these events happened first". It would take
a little care to use properly, however.

Regards,
Jeff Davis