Implicit conversion/comparision of timestamp with and without timezone

From: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Implicit conversion/comparision of timestamp with and without timezone
Date: 2009-06-20 20:28:34
Message-ID: alpine.LFD.2.00.0906202215100.14865@bbs.intern
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

It is unclear to me how implicit conversion/comparision of timestamp with
and without timezone works.

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

SELECT
*
FROM
table
WHERE
-- currently in UTC+02 timezone, entries in UTC+01 timezone
-- => works well with UTC+01 entry timezone, but why?
datetime >= '2009-03-09 00:00:00.0'
AND datetime < '2009-03-10 00:00:00.0'
;

-- currently in UTC+02 timezone, entries in UTC+02 timezone
-- => works well with UTC+02 entry timezone, but why?
datetime >= '2009-06-12 00:00:00.0'
AND datetime < '2009-06-13 00:00:00.0'
;

-- Same result, unclear why
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'

-- Same result, unclear why
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'

-- Same result2, unclear why
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'

-- Same result2, unclear why
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'

How is implicit conversion done? With timezone of datetime or timezone of
now() or timezone?

Would it make a difference when datetime would be declared with TIMESTAMP
WITHOUT TIME ZONE?
--------------------------------------------------

Thnx.

Ciao,
Gerhard

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2009-06-21 02:57:51 Re: looping over a small record set over and over in a function
Previous Message Pavel Stehule 2009-06-20 20:01:23 Re: pl/sql resources for pl/pgsql?