Re: Why data of timestamptz does not store value of timezone passed to it?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>
>> But the standard doesn't say anything about storing a time zone
>> *name* or *abbreviation* -- it requires that it be stored as UTC
>> with the *offset* (in hours and minutes).  That makes it pretty
>> close to what we have -- it's all about a difference in
>> presentation.  And as far as I can see it completely dodges the
>> kinds of problems you're talking about.
>
> However, the added field creates its own semantic problems.
> As an example, is 2014-08-28 18:00:00-04 the same as or different from
> 2014-08-28 17:00:00-05?  If they're different, which one is less?
> If they're the same, what's the point of storing the extra field?
> And do you really like "equal" values that behave differently,
> not only for I/O but for operations such as EXTRACT()?
>
> (I imagine the SQL spec gives a ruling on this issue, which
> I'm too lazy to look up; my point is that whatever they did, it
> will be the wrong thing for some use-cases.)

I think (based on your earlier post) that we agree that would have
been better to implement the type named in the standard according
to the definition given in the standard (and to use a new type name
for the more generally useful behavior PostgreSQL currently uses
for timestamptz), but that it's too late to go there now.  QUEL's
relational calculus is superior in just about every way to SQL, but
if we're going to go with the standard because it *is* a standard,
then let's freaking *do* it and extend where beneficial. Otherwise,
why switch from QUEL in the first place?

It was actually rather disappointing to hear that we had a
conforming implementation and changed away from it circa the 7.2
release; and even more disturbing to hear that decision is still
being defended on the grounds that there's no point providing
standard conforming behavior if we can think of different behavior
that we feel is more useful.  We should have both.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company