Re: Implicit conversion/comparision of timestamp with and without timezone

Gerhard Wiesinger <lists@wiesinger.com> writes:
> Ok, the "problem" is:
> --------------------------------------------------
>        datetime >= '2009-03-09 00:00:00.0'
>    AND datetime  < '2009-03-10 00:00:00.0'
> -- Index Cond: ((datetime >= '2009-03-09 00:00:00+01'::timestamp with time zone) AND (datetime < '2009-03-10
00:00:00+01'::timestampwith time zone)) 
> --------------------------------------------------
>        datetime >= '2009-04-01 00:00:00.0'
>    AND datetime  < '2009-04-02 00:00:00.0'
> -- Index Cond: ((datetime >= '2009-04-01 00:00:00+02'::timestamp with time zone) AND (datetime < '2009-04-02
00:00:00+02'::timestampwith time zone)) 

> I would have expected that the 1st conversion is done with my current
> timezone of the query time (CEST=+02).

Well, it is being done with your current timezone ... but evidently with
a daylight-savings-time-aware definition of what that is.  If you really
want to dumb this down to not be DST aware, set your timezone setting
to a non-DST-aware value.  Perhaps the following will make it a bit
clearer what's happening:

regression=# set timezone to 'Europe/Vienna';
SET
regression=# select '2009-03-09 00:00:00.0'::timestamptz;
      timestamptz
------------------------
 2009-03-09 00:00:00+01
(1 row)

regression=# select '2009-04-01 00:00:00.0'::timestamptz;
      timestamptz
------------------------
 2009-04-01 00:00:00+02
(1 row)

regression=# set timezone to 'CEST-02';
SET
regression=# select '2009-03-09 00:00:00.0'::timestamptz;
      timestamptz
------------------------
 2009-03-09 00:00:00+02
(1 row)

regression=# select '2009-04-01 00:00:00.0'::timestamptz;
      timestamptz
------------------------
 2009-04-01 00:00:00+02
(1 row)

See here for more detail:
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES

            regards, tom lane