[NOVICE] Does pg store all `timestamp with time zone` in localtime? Why?

Поиск
Список
Период
Сортировка
От Yann Salaün
Тема [NOVICE] Does pg store all `timestamp with time zone` in localtime? Why?
Дата
Msg-id CAHDVCz6O2OKpt2joLpBkHdVfVWK__Y6dOO-e+KYi7q5fv20GWA@mail.gmail.com
обсуждение исходный текст
Ответы Re: [NOVICE] Does pg store all `timestamp with time zone` inlocaltime? Why?
Список pgsql-novice
Дерево обсуждения
Hello,

I was surprised to observe this behavior in psql (my time zone is GMT+2)

psql (9.5.8)
Type "help" for help.

db=# create table t(created_at timestamp with time zone);
CREATE TABLE
db=# insert into t(created_at) values ('2017-08-24 12:00:00 +02:00');
INSERT 0 1
db=# insert into t(created_at) values ('2017-08-24 12:00:00 +03:00');
INSERT 0 1
db=# select created_at from t;
       created_at       
------------------------
 2017-08-24 12:00:00+02
 2017-08-24 11:00:00+02
(2 rows)

Both timestamps are printed in my local tz, even if I specify a different tz when I insert them. I understand that pg does the correct conversion to the local tz, but I would expect that it prints the tz information that I inserted.

In short, I would expect the last statement to return the following:

db=# select created_at from t;
       created_at       
------------------------
 2017-08-24 12:00:00+02
 2017-08-24 12:00:00+03
(2 rows)

I wonder if the conversion is done before the storage, or before the printing, ie. if the original tz information is lost. In addition, I would be very interested to read discussions that led to this design decision.

Thank you very much for your answers,

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