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

On Sun, 21 Jun 2009, Richard Huxton wrote:

> Gerhard Wiesinger wrote:
>> Hello,
>>
>> It is unclear to me how implicit conversion/comparision of timestamp with
>> and without timezone works.
>
> It's not entirely clear where the problem is. You don't say the results
> you're getting or what you thought they should be.
>

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). I don't see any problem with this
since all ugly timezone calculation are already done by PostgreSQL, but it
is nowhere (?) documented and was quite confusing me.

So for me documentation looks like:
A timestamp without any timezone information is converted into a
timestamp with time zone information (explicitly or implicitly) with the
with the time zone from the timestamp which has to be converted (and not
from the time zone from now()). The date and time information is not touched.

And:
A timestamp with timezone information is converted into a timestamp
without timezone information by removing just the timezone information. So
the date and time information is not touched.

Correct?

BTW:
I'm in CET (now CEST) timezone which is changed on last sunday in march
2:00 CET (and last sunday in October 3:00 CEST).

show timezone;
    TimeZone
---------------
  Europe/Vienna

>> --------------------------------------------------
>> -- datetime TIMESTAMP WITH TIME ZONE
>> -- datetime entries are with UTC+01 and UTC+02 done
>> -- 2009-03-09: UTC+01
>> -- 2009-06-12: UTC+02
>> -- current timezone: UTC+02
>
> OK, so I've got this:
> => show timezone;
> TimeZone
> ----------
> UTC+02
>
> SELECT * FROM tstest;
>           d
> ------------------------
> 2009-03-08 23:00:00-02
> 2009-06-12 00:00:00-02
> (2 rows)
>
> So - it's showing the timestamp with timezones you mentioned but in the
> current timezone. The UTC+02 actually being a -02 offset is just part of the
> standards afaik. Note that midnight 2009-03-09 is 2009-03-08 in the current
> timezone.
>

SELECT datetime FROM table ORDER BY datetime DESC LIMIT 1;
         datetime
------------------------
  2009-06-21 14:54:00+02

So it should also be +02 at your test. Why is it -02 at your test?

BTW:
2009-03-09 and 2009-03-08 are both in CET timezone in Europe (see above).

> So - if we run EXPLAIN on your queries that should show us how the values are
> getting typecast.
>
> => EXPLAIN SELECT * FROM tstest WHERE d >= '2009-03-09 00:00:00.0' AND d <
> '2009-03-10 00:00:00.0';
>                                                             QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on tstest  (cost=0.00..42.10 rows=11 width=8)
>   Filter: ((d >= '2009-03-09 00:00:00-02'::timestamp with time zone) AND (d
> < '2009-03-10 00:00:00-02'::timestamp with time zone))
>
>
> This comes back with zero rows because without an explicit timezone it
> assumes our current one.
>
>
> => EXPLAIN SELECT * FROM tstest WHERE d >= '2009-06-12 00:00:00.0' AND d <
> '2009-06-13 00:00:00.0';
>                                                             QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on tstest  (cost=0.00..42.10 rows=11 width=8)
>   Filter: ((d >= '2009-06-12 00:00:00-02'::timestamp with time zone) AND (d
> < '2009-06-13 00:00:00-02'::timestamp with time zone))
>
> This will match one row.
>
> Repeating the EXPLAIN on your other queries should show you what's happening.
> If I've missed the point of your question, can you say what results you get
> and what you think they should be.
>

See above.

For completeness:
--------------------------------------------------
       datetime >= TIMESTAMP WITHOUT TIME ZONE '2009-03-09 00:00:00.0'
   AND datetime  < TIMESTAMP WITHOUT TIME ZONE '2009-03-10 00:00:00.0'
-- Index Cond: ((datetime >= '2009-03-09 00:00:00'::timestamp without time zone) AND (datetime < '2009-03-10
00:00:00'::timestampwithout time zone)) 

=> Afterwars an implicit conversation to timestamp with time zone with the
timezone from the timestamp to be converted is done. (See my "docs"
above).

--------------------------------------------------
       datetime >= TIMESTAMP WITH TIME ZONE '2009-03-09 00:00:00.0'
   AND datetime  < TIMESTAMP WITH TIME ZONE '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 >= TIMESTAMP WITHOUT TIME ZONE '2009-06-12 00:00:00.0'
   AND datetime  < TIMESTAMP WITHOUT TIME ZONE '2009-06-13 00:00:00.0'
--   Index Cond: ((datetime >= '2009-06-12 00:00:00'::timestamp without time zone) AND (datetime < '2009-06-13
00:00:00'::timestampwithout time zone)) 

Same as above.

--------------------------------------------------
       datetime >= TIMESTAMP WITH TIME ZONE '2009-06-12 00:00:00.0'
   AND datetime  < TIMESTAMP WITH TIME ZONE '2009-06-13 00:00:00.0'
--  Index Cond: ((datetime >= '2009-06-12 00:00:00+02'::timestamp with time zone) AND (datetime < '2009-06-13
00:00:00+02'::timestampwith time zone)) 


Ciao,
Gerhard

--
http://www.wiesinger.com/