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