Re: Unexpected behaviour of date_part

Richard Huxton wrote:
> > test=> SELECT date_part('timezone_hours', timestamp with time zone '2009-06-26 10:05:57.46624+11');
> > date_part
> > -----------
> > 2
> > (1 row)
> >
> > 2 being the offset of my local time zone.
> >
> > Now an EXPLAIN shows that this is due to the fact that the timestamp
> > is converted to my local time zone before it is submitted to the function,
> > but I think that this result is undesirable and misleading.
>
> Basically, "timestamp with time zone" is a bad name for the type. If it
> was called "absolute time" the behaviour would make sense. The query
> below returns true, which makes sense if they are absolute times.
>
> SELECT '29/06/2009 10:54:55+01'::timestamptz =
> '29/06/2009 11:54:55+02'::timestamptz;
>
> What would be useful sometimes is a type "timestamp AND time zone" which
> stored each separately and where the above wouldn't be true. I think
> it's been discussed, but no-one has done the necessary work on it.

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:

ISO/IEC 9075-2:2003, chapter 6.27 <numeric value function>,
General rule 4) b)
(this describes the behaviour of EXTRACT):

b) Otherwise, let TZ be the interval value of the implicit or explicit time zone displacement associated
with the <datetime value expression>.
Case:
i) If <extract field> is TIMEZONE_HOUR, then the result is calculated as EXTRACT (HOUR
FROM TZ).
ii) Otherwise, the result is calculated as EXTRACT (MINUTE FROM TZ)

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?

Yours,
Laurenz Albe