From: | Yann Salaün <yannsalaun1(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Does pg store all `timestamp with time zone` in localtime? Why? |
Date: | 2017-08-24 12:52:16 |
Message-ID: | CAHDVCz6O2OKpt2joLpBkHdVfVWK__Y6dOO-e+KYi7q5fv20GWA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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,
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2017-08-24 13:00:02 | Re: Does pg store all `timestamp with time zone` in localtime? Why? |
Previous Message | Michael Anderson | 2017-08-23 08:14:26 | [WAL-Archive recovery stuck on one log file] |