Thread: Unexpected behaviour of date_part

This is PostgreSQL 8.4, but the behaviour has not changed from earlier versions:

test=> SHOW timezone;
   TimeZone    
---------------
 Europe/Vienna
(1 row)

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.

Yours,
Laurenz Albe

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

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

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

Richard Huxton <dev@archonet.com> writes:
> 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.

The spec does appear to contemplate that the timezone be represented
separately.  We've discussed this in the past but there's not been a lot
of enthusiasm for changing it ... aside from the work involved, it would
mean doubling the space required for a timestamptz value (because of
alignment considerations).

            regards, tom lane