Postgresql stores timezone information in TIMESTAMP WITH TIMEZONE?
NickName:Alex Vyushkov Ask DateTime:2017-01-09T03:24:44

Postgresql stores timezone information in TIMESTAMP WITH TIMEZONE?

I'm trying to understand how PostgreSQL handles TIMESTAMP WITH TIMEZONE type, and I 'm running into the discrepancy between theory and practice.

According to the documentation and other stack overflow posts,

For timestamp with time zone, the internally stored value is always in UTC ... When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone.

I believe this means that all timestamps in any given SELECT statement will be in the same/current timezone. However, this is what I see: enter image description here

interview_timestamp is TIMESTAMP WITH TIMEZONE. This blows my mind. 

Copyright Notice：Content Author:「Alex Vyushkov」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/41536821/postgresql-stores-timezone-information-in-timestamp-with-timezone

Answers
Laurenz Albe 2017-01-09T12:38:19

It is strange that you sometimes see a time zone offset and sometimes not, I would blame that on the GUI you are using. Try with psql and see how the data look there.\n\nThe offset “-05:44:38” is perfectly normal, since time zones were introduced in the late 19th century and local solar time is used before that.",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “Postgresql stores timezone information in TIMESTAMP WITH TIMEZONE?” related questions
Postgresql stores timezone information in TIMESTAMP WITH TIMEZONE?

I'm trying to understand how PostgreSQL handles TIMESTAMP WITH TIMEZONE type, and I 'm running into the discrepancy between theory and practice. According to the documentation and other stack over...

Show Detail
What timezone information does PostgreSQL store?

The PostgreSQL documentation is fairly thorough and useful: https://www.postgresql.org/docs/9.2/datatype-datetime.html#DATATYPE-TIMEZONES but seems to overlook clarity on a rather useful point wh...

Show Detail
Storing a timestamp with a custom timezone

I have to store an "event time" that could be in any timezone. By default rails stores these in the DB as UTC and strips off our custom timezone. When we read the attribute back it is converted to ...

Show Detail
postgresql numeric to timestamp conversion timezone issue

I am trying to convert a numeric to timestamp in postgresql. However, it always converts it in EST timezone. Before running the query, I try the following. set time zone 'UTC+10'; select to_times...

Show Detail
How to know a timezone of a timestamp in postgresql 8.3

I'm using postgresql 8.3 and i would like to know the timezone of a particular timestamp (a column in a table). In the documentation i've found the keyword "timezone" But i don't understand how to

Show Detail
Timezone in PostgreSQL

I have a column time_stamp of type timestamp without time zone. While executing select now()::timestamp(0) it returns a value with PST timezone. But my system timezone is IST. I need the same in

Show Detail
JPQL and timestamp with timezone

I have the following field definitions in one of my JPA entities: @Column(nullable = false, name = "start_time", columnDefinition= "TIMESTAMP WITH TIME ZONE") @Temporal(javax.persistence.TemporalT...

Show Detail
postgresql: numeric to timestamp issue with timezone

I have extracted some emails from gmail into postgresql. the dates are stored in numeric. some few sample dates are 1399586523000 1399773692000 1400191333000 1400378501000 1400796072000 140100848...

Show Detail
Postgres timestamp with timezone

I have column 'event_date_time' in my 'events' table with type 'timestamp with timezone'. My python flask application is saving date like '2014-08-30 02:17:02+00:00' but postgres automatically conv...

Show Detail
Postgresql SELECT CURRENT_TIMESTAMP does not show timezone

Originally i have a timestamp column named "created_at" set with the following: Type : timestamp with time zone default value : transaction_timestamp() However, i noticed that the value of "creat...

Show Detail