Implicit conversion/comparision of timestamp with and without timezone

Поиск
Список
Период
Сортировка
От Gerhard Wiesinger
Тема Implicit conversion/comparision of timestamp with and without timezone
Дата
Msg-id alpine.LFD.2.00.0906202215100.14865@bbs.intern
обсуждение исходный текст
Ответы Re: Implicit conversion/comparision of timestamp with and without timezone  (Richard Huxton <dev@archonet.com>)
Список 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/


В списке pgsql-general по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: pl/sql resources for pl/pgsql?
Следующее
От: Craig Ringer
Дата:
Сообщение: Re: looping over a small record set over and over in a function