Re: Top five challenges

silly sad wrote:
> On 03/10/11 19:01, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> >> Excerpts from sad's message of mi? mar 02 10:23:53 -0300 2011:
> >>> On Mar 2 2011, Josh Berkus wrote:
> >>
> >>>>> 3. To make TIMESTAMPTZ useful by introducing proper type cast
> >>>>> TIMESTAMPTZ to TIMESTAMP
> >>>>
> >>>> Why are you using Timestamp-no-tz at all?  I was thinking we should
> >>>> change the default ...
> >>>
> >>> Why did u introduced it?
> >>
> >> The standard requires the current behavior.  It's not going to change.
> >> It changed in 7.1 or so.
> >
> > And we document why the default is so odd:
> >
> >     http://www.postgresql.org/docs/9.0/static/datatype-datetime.html#DATATYPE-TIMEZONES
> >
> >     Note: The SQL standard requires that writing just timestamp be
> >     equivalent to timestamp without time zone, and PostgreSQL honors that
> >     behavior. (Releases prior to 7.3 treated it as timestamp with time
> >     zone.)
>
> do you document why 'epoch'::timestamp is not the true 'epoch' unless
> timezone is not GMT?
>
> if you mention any timestamp except 'epoch' it will be interpreted
> correctly taking in account timezone setting.

Well, 'epoch' clearly is a point in time with the hour being midnight at
GMT, so I don't see a problem with epoch making such an adjustment:

    test=> select 'epoch'::timestamp ;
          timestamp
    ---------------------
     1970-01-01 00:00:00
    (1 row)

    test=> select 'epoch'::timestamptz;
          timestamptz
    ------------------------
     1969-12-31 19:00:00-05
    (1 row)

However, a text string behaves the same:

    test=> select '1970-01-01 00:00:00'::timestamp;
          timestamp
    ---------------------
     1970-01-01 00:00:00
    (1 row)

    test=> select '1970-01-01 00:00:00'::timestamptz;
          timestamptz
    ------------------------
     1970-01-01 00:00:00-05
    (1 row)

Notice the "-05".  I would argue that 'epoch' has a predefined timezone.
We don't document this because it has never been mentioned before and no
one has mentioned they were surprised by the behavior.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +