Re: Unexpected behaviour of date_part

Albe Laurenz 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.

--
Richard Huxton
Archonet Ltd