Обсуждение: [NOVICE] Does pg store all `timestamp with time zone` in localtime? Why?
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 TABLEdb=# insert into t(created_at) values ('2017-08-24 12:00:00 +02:00');INSERT 0 1db=# insert into t(created_at) values ('2017-08-24 12:00:00 +03:00');INSERT 0 1db=# select created_at from t;created_at------------------------2017-08-24 12:00:00+022017-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:
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.db=# select created_at from t;created_at------------------------2017-08-24 12:00:00+022017-08-24 12:00:00+03(2 rows)
Thank you very much for your answers,
Yann Salaün wrote: > Hello, > > I was surprised to observe this behavior in psql (my time zone is GMT+2) No, it stores in UTC and converts back and forth as you store then in and as you read them out. It depends on the TimeZone configuration parameter each time. The TZ that was current when the value was stored, is itself NOT stored in the same column. You could add a column to store that TZ, if you wanted. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Threre's a really good explanation of the data type, it's implementation, usage and how to insert/select values:
(This was one of those "this is why I love Postgres" moments!)
On 24 August 2017 at 14:00, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Yann Salaün wrote:
> Hello,
>
> I was surprised to observe this behavior in psql (my time zone is GMT+2)
No, it stores in UTC and converts back and forth as you store then in
and as you read them out. It depends on the TimeZone configuration
parameter each time. The TZ that was current when the value was stored,
is itself NOT stored in the same column. You could add a column to
store that TZ, if you wanted.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Thank you Greg for this exciting read.
On Thu, Aug 24, 2017 at 8:07 PM, Greg Robson <gregrobson@gmail.com> wrote:
Threre's a really good explanation of the data type, it's implementation, usage and how to insert/select values:(This was one of those "this is why I love Postgres" moments!)On 24 August 2017 at 14:00, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:Yann Salaün wrote:
> Hello,
>
> I was surprised to observe this behavior in psql (my time zone is GMT+2)
No, it stores in UTC and converts back and forth as you store then in
and as you read them out. It depends on the TimeZone configuration
parameter each time. The TZ that was current when the value was stored,
is itself NOT stored in the same column. You could add a column to
store that TZ, if you wanted.--
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice