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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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