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

On 08/28/2014 02:25 PM, Kevin Grittner wrote:
> 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.

Except that an offset is not a timezone. This is why the spec behavior
was always academic crippleware, and why we abandoned it back in ~~7.2.
It does me no good at all to know that a timestamp is "offset -07:00":
that could be Mountain Time, Arizona Time, or Navajo Nation time, all of
which will behave differently when I add 2 months to them.

Unless the only goal is to be compatible with some other DBMS, in which
case ... build an extension.

On the other hand, I take partial responsibility for the mess which is
our data type naming. What we call timestamptz should just be
"timestamp", and whether or not it converts to local timezone on
retrieval should be a GUC setting. And the type we call "timestamp"
shouldn't exist. Hindsight is 20/20.

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