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

From: Richard Huxton <dev(at)archonet(dot)com>
To: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Implicit conversion/comparision of timestamp with and without timezone
Date: 2009-06-21 11:20:36
Message-ID: 4A3E1784.908@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Sergio Borgonovo 2009-06-21 12:57:16 cursors, temp tables, dynamic sql technique (was and is: looping over a small record set over and over in a function)
Previous Message Peter Eisentraut 2009-06-21 07:42:47 Re: pl/sql resources for pl/pgsql?