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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +