Re: Unexpected behaviour of date_part

Albe Laurenz wrote:
> Richard Huxton wrote:
>>> test=> SELECT date_part('timezone_hours', timestamp with time zone '2009-06-26 10:05:57.46624+11');

> I like your suggestion of "absolute time", which makes PostgreSQL's
> timestamptz much easier to understand.
>
> What worries me a bit is that the SQL standard, which we try to adhere
> to, seems to suggest something else:

> b) Otherwise, let TZ be the interval value of the implicit or explicit time zone displacement associated
> with the <datetime value expression>.

> I'd say that "the interval value of the explicit time zone displacement"
> associated with the timestamp in my example above is an interval of +11 hours.
>
> Or can you reconcile this with PostgreSQL's behaviour?

The <datetime value expression> isn't '2009 ... +11', it's the absolute
time that string represents. It doesn't in fact have a time-zone
component except in the context of your locale settings.

I don't know if we do follow the standard here though - not read it through.
--
Richard Huxton
Archonet Ltd