How to read timezone from 'timestamp with time zone' column?

Question

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 provide my own calendar. I have see no way to obtain that calendar from timestamp field I am reading.

I am working on system that stores time data of multiple timezones. I need to save data with timezone information, and be able to read that timezone back from database.

Is it possible without hacks like

• storing timezone value in another field
• storing date as string, like 'Wed 17 Dec 07:37:16 1997 PST'

I am using JDBC 41 (JDBC4 Postgresql Driver, Version 9.4-1201), java 8.

Stephens

Answer 1

The PostgreSQL documentation here says that:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).

So there is no need to "store the time zone [that corresponds to the timestamp value]" per se; the timestamp with time zone values are always stored as UTC.

Also, there is no need to "obtain the Calendar from the timestamp field". The purpose of the Calendar is for you to define the particular timezone that you want to work with in your Java application.

In other words, timestamp with timezone does not store values from various timezones (e.g., some in EST, others in PST, etc.), it converts everything to UTC when the timestamp values are inserted.

Coplanar

Answer 2

Accepted answer is true and accurate. timestamp with time type does not store timezone information in the field, and it is not possible to extract it.

If interested in timezone of the timestamp, it must be stored in separately (in other field, or in custom column type).

At first glance, it looks like that timezone may be extracted from timestamp with timezone using function extract(timezone from field), but it is not the case.

That function just gives 'time zone offset from UTC, measured in seconds'. Important (and not stated in documentation) part is that the offset is measured from current timezone (set by session SET SESSION TIME ZONE, or server timezone if not set). It is not offset that was used when saving field.

Stephens

[1] This urnderstanding is correct. Regarding your Question’s phrasing, storing the time zone of the input/source data in anothe field/column is not at all a hack, that is the correct and logical approach if you truly are interested in the original zone. [2] Big tip: Avoid java.util.Date and .Calendar. Learn to use the java.time package in Java 8 and later, and/or Joda-Time. – Male

Thanks, now I understand why timezone must be stored in another field. I would prefer to store timezone along with my timestamp value. 'timestamp with timezone' is quite unfortunate name of db datatype -it looks like it stores timezone. Java 8 offers class ZonedDateTime for storing datetimewith timezone. – Stephens

Yes the SQL types TIMESTAMP WITH TIME ZONE (and WITHOUT) are poorly named. I think of it as "timestamp with respect for time zone". Any time zone info accompanying input date-time is used to adjust to UTC in WITH type but ignored in WITHOUT type. Same for output from database, the SQL session’s current time zone setting is applied to value coming out of WITH type but not for WITHOUT type. The WITHOUT type represents “local” date-time values, of no particular time zone, like saying Christmas starts midnight December 25 this year -- a different moment in each local place in various time zones. – Male

See Postgres doc for details (regardless of your database). – Male