CODEWITHSUNDEEP
postgresqltimezone
semantic-dev
semantic-dev

Reputation: 1123

How to know a timezone of a timestamp in postgresql 8.3

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 ?

Upvotes: 17

Views: 20309

Answers (4)

Ashish
Ashish

Reputation: 141

In Postgres the original input timezone is lost even if you store the datetime value in timestamp with time zone data type.

Disadvantage:
Say if I have datetime data(along with timezone info) of when people use their facebook app all over the world. Now I store those values in timestamp with time zone data type. One random day I want to see how heavily people use their FB app in the morning compared to at night and if the same trend is seen across countries. But wait, I no longer have the timezone information. Sitting in D.C., what I can see is how the activity panned out all over the world when it was 10am EST.

Advantage:
If you are comparing two datasources with timestamp of same timezone but one was stored as what the local watch was showing while other stored by converting it to UTC. Here what you can do is just store the timestamp in the timestamptz datatype and tell which timezone it belongs to and then you can compare them easily.
For example,
An activity in PST timezone was recorded at 2014-10-19 10:23:54. Now two separate data sources stored it separately. Datasource1 stored it as 2004-10-19 10:23:54 PST, Datasource2 stored it as 2014-10-19 18:23:54 UTC. If they are stored in the timestamptz datatype, it will show you the same time when you do 

SELECT datasource1.time, datasource2.time

Upvotes: 5

Dennis
Dennis

Reputation: 757

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.

Upvotes: 0

W Strzalka
W Strzalka

Reputation: 196

"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.

Upvotes: 13

Johannes Weiss
Johannes Weiss

Reputation: 54021

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.

Upvotes: 18

Related Questions