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

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,

Responses

Browse pgsql-novice by date

  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]