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

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.

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

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.

--
Richard Huxton
Archonet Ltd