How to know a timezone of a timestamp in postgresql 8.3

Question

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 apply it in a column of table. Is it possible ?

Answer 1

I assume you have a column named ct which has the type TIMESTAMPTZ in the table t. Then you can use:

SELECT EXTRACT(TIMEZONE FROM ct) FROM t;

to get the offset of the timezone in seconds. It that gives you 3600 from UTC/GMT that means either GMT+1, CET or whatever. The value returned depends on your TIMEZONE setting.

Sample (I live in Germany, actual timezone ist GMT+1/CET):

test=# select '2008-01-01 12:00:00 GMT+5'::timestamptz;
      timestamptz       
------------------------
 2008-01-01 18:00:00+01

test=# set timezone to 'gmt';
SET
test=# select '2008-01-01 12:00:00 GMT+5'::timestamptz;
      timestamptz       
------------------------
 2008-01-01 17:00:00+00

As you can see it always outputs anything in the configured timezone. So the offset you will get with EXTRACT(TIMEZONE FROM ...) depends on your TIMEZONE setting. The timezone which was given on INSERT is lost, because it is not worth to be saved. The thing that counts is that everything is correct and that should not depend on the TIMEZONE setting. PostgreSQL does that very well.

Answer 2

"PostgreSQL does that very well."

I really like PostgreSQL, but in this particular feature it does not do it well. Timezone is not only offset to GMT. Timezone is tight to political rules that implies daylight saving. As there are plenty of timezones with the same offset and different daylight saving rules - when PG forgets about original timezone it looses the information in fact.

Personally I store original timezone separately for the dates it matters in the form 'America/New_York'. If anybody has better solution - it's welcome.