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

Gerhard Wiesinger <lists(at)wiesinger(dot)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'::timestamp with 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'::timestamp with 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