Re: Converting to UTC multiple times converts back to local time zone

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Converting to UTC multiple times converts back to local time zone
Дата
Msg-id CAKFQuwYeHxefXOWmF_fXOM=MfR=QOz=Uas-HNz5_fA=R-koUfw@mail.gmail.com
обсуждение исходный текст
Ответ на Converting to UTC multiple times converts back to local time zone  (Gary Bernhardt <gary.bernhardt@gmail.com>)
Ответы Re: Converting to UTC multiple times converts back to local time zone  (Gary Bernhardt <gary.bernhardt@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
On Mon, Jun 25, 2018 at 4:47 PM, Gary Bernhardt <gary.bernhardt@gmail.com> wrote:

testdb=# select (now() at time zone 'utc') at time zone 'utc';
2018-06-25 16:43:03.200762-07
 
I'd expect "converting" a UTC timestamp to UTC would keep it in UTC. Am I missing something?
​
Not a bug - the behavior is documented here:

​https://www.postgresql.org/docs/9.6/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Specifically, for the point-in-time types whenever one type is deferenced using AT TIME ZONE the alternate type is output.

SELECT pg_typeof(now()); -- timestamp with time zone (timestamptz)
SELECT pg_typeof(now() at time zone 'utc'); -- timestamp without time zone​ (timestamp)

I'll agree that this was a surprising finding for me too, but in the overall scheme of how PostgreSQL works, and other features it provides (e.g., TimeZone GUC and to_char()) it fits.

David J.
​
P.S. All timestamptz values are stored at UTC without knowledge of the original timezone.

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

Предыдущее
От: Gary Bernhardt
Дата:
Сообщение: Converting to UTC multiple times converts back to local time zone
Следующее
От: Gary Bernhardt
Дата:
Сообщение: Re: Converting to UTC multiple times converts back to local time zone