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.

Answer 3

Since the timestampz is recoreded for an instant in time in ZULU/GMT time, which never changes it's offset (since it's the reference), it is not necessary to record the timezone. You need only add/subtract the offset to the GEOPOLITICAL time zone offset in the PAST, PRESENT, or FUTURE.

You DO need to know the exact GEOPOLITICAL TIMEZONE in effect at the location the time applies to at the moment applies to for PAST and PRESENT purposes.

For future instances in time purposes, this gets more problematic, maybe. It should still work though. Think of sunset. If some location earth has a sunset @ midnight ZULU time (somewhere over the Atlantic Ocean or Northern Canada to Alaska in winter in the Northern Hemisphere), and that time is assumed to be 8 PM ( -4:00 offset) in that location at the moment you record it in the system and you record it as 'winter-date-in-future 8:00 PM' it will be recorded as 24:00 GMT in the database.

Now, that location on earth gets a hair up its *ss and thumbs its nose at geographical related time reckoning, and calls their time zone - '+11:55'. So for them when it's midnight in England (GMT midnight) they WANT to call it 11:55 AM, totally their choice. When any computer wants to display that date in the future for that location (i.e. that geopolitical timezone), they will call it 11:55 AM, even if the sun is setting. And of course, it WILL be the day AHEAD of the day you planned it :-) Their problem.