Can you recover the full timezone name from a Postgresql TIMESTAMP WITH TIME ZONE field?
NickName:jl6 Ask DateTime:2014-01-27T02:05:07

Can you recover the full timezone name from a Postgresql TIMESTAMP WITH TIME ZONE field?

Take the following example:

create temporary table t1 (c1 timestamp with time zone);
insert into t1 values ('2003-04-12 04:05:06 America/New_York'::timestamp with time zone);

If I'm reading the documentation correctly, here Postgresql will use the full timezone name to convert the timestamp to UTC by adding +05:00 hours, then store that.

But if that's true then I can't distinguish between 2003-04-12 04:05:06 America/New_York and 2003-04-12 04:05:06 America/Panama, which has the same UTC offset but a different daylight savings offset.

Is that right?

Copyright Notice：Content Author:「jl6」，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/21367180/can-you-recover-the-full-timezone-name-from-a-postgresql-timestamp-with-time-zon

Answers
Pavel Stehule 2014-01-26T19:18:11

PostgreSQL doesn't store original time zone. You can do it as separate column when you need it.\n\nhttp://postgres.cz/wiki/PostgreSQL_SQL_Tricks_III#Domain_for_time_zone\n\nCITEXT is case insensitive text type from PostgreSQL contrib package. You can use a text instead if you don't want to install it.",

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 “Can you recover the full timezone name from a Postgresql TIMESTAMP WITH TIME ZONE field?” related questions
Can you recover the full timezone name from a Postgresql TIMESTAMP WITH TIME ZONE field?

Take the following example: create temporary table t1 (c1 timestamp with time zone); insert into t1 values ('2003-04-12 04:05:06 America/New_York'::timestamp with time zone); If I'm reading the

Show Detail
PostgreSQL Parameter error (timestamp with time zone, timestamp without time zone)

I get an error Caused by: org.postgresql.util.PSQLException: ERROR: function months_between(timestamp with time zone, timestamp without time zone) does not exist Hint: No function matches the given

Show Detail
PostgreSQL Parameter error (timestamp with time zone, timestamp without time zone)

I get an error Caused by: org.postgresql.util.PSQLException: ERROR: function months_between(timestamp with time zone, timestamp without time zone) does not exist Hint: No function matches the given

Show Detail
Create a timestamp with time zone in PostgreSQL from Liquibase XML

I have a Liquibase migration XML file which creates columns with the datetime type. <createTable tableName="foo"> <column name="bar" type="datetime"/> </createTable&

Show Detail
PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone

I faced with the following issue this morning: select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'EST5EDT'; returns me 2011-12-30 05:30:00+00 witch is wrong. But next queries

Show Detail
How to update timestamp with timezone field in postgresql using c#

How to update a postgresql's timestamp with timezone data field with the C# code? I expect datetime with timezone using codes below: DateTime _now = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.

Show Detail
timestamp with time zone (postgresql)

I have a field named 'timestamp_created' with field type timestamp with time zone. I've set the postgrsql timezone to UTC with the command: SET TIME ZONE UTC;. When I do the following command: SELE...

Show Detail
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
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
How to read timezone from 'timestamp with time zone' column?

I am unable to find a way to read timezone value in PostgreSQL column of type timestamp with time zone. JDBC offers method java.sql.ResultSet#getTimestamp(int, java.util.Calendar) but I must provi...

Show Detail