Re: storing TZ along timestamps

Alvaro, Kevin,

>> In a builtin data type, which of those three would you pick? Only the
>> application knows.
>
> I think this whole discussion is built on the assumption that the client
> timezone and the application timezone are one thing and the same; and
> the server timezone is not relevant at all. If the app TZ is not the
> client TZ, then the app will need fixed.

Not at all. Consider a hosted webapp where the user is allowed to set
their own timezone, but you use pooled connections. In that case, the
app is going to be handling user timezones with an AT TIME ZONE, not
with a SET TIMEZONE=""

> I have my doubts about that, and I hope not. These details haven't been
> discussed at all; I only started this thread to get community approval
> on cataloguing the TZs.

I am strongly in favor of having a *timezone* data type and some system
whereby we can uniquely identify timezones in the Zic database. That
would be tremendously useful for all sorts of things. I'm just
asserting that those who want a composite timestamp+saved-time-zone data
type have not thought about all of the complications involved.

> So, if you're grabbing a timestamp and the time zone for it, how do
> you ensure you've done that atomically if you're at the boundary of
> a DST change? The difficulty of grabbing both such that they are
> guaranteed to correspond suggests to me that they really form a
> single logical value.

Not relevant, given that (hopefully) the conception of a time zone
should exist independantly of whether it's currently in DST or not.
That is, the time zone is NOT "-07". The time zone is "US/Pacific".

> Why? I think you'd want to add some *new* casts and operators for
> the new data type; I don't see why any existing ones would need to
> be modified.

That would work too. What I'm pointing out is that we can't implement
the new type using just one-line modifications to the old operators and
functions.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com