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.

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.

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.