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

On Thu, Aug 28, 2014 at 03:25:49PM -0700, Josh Berkus wrote:
> 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.

Well, the standard TIMESTAMP requires WITHOUT TIME ZONE, so I don't know
how you would be standards-compliant without it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +