Implicit conversion/comparision of timestamp with and without timezone

[Gerhard Wiesinger <lists@xxxxxxxxxxxxx>]

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/


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general