How to find out the timezone of a timestamp in postgresql 8.3

Question

I am using postgresql 8.3, and I would like to know the time zone of a specific timestamp (column in the table).

In the documentation, I found the keyword "time zone"

But I do not understand how to apply it in a table column. Is it possible?

Answer 1

I assume that you have a column named ct that is of type TIMESTAMPTZ in table t . Then you can use:

 SELECT EXTRACT(TIMEZONE FROM ct) FROM t; 

to get the time zone offset in seconds. This gives you 3600 from UTC / GMT , which means either GMT+1 , CET , or whatever. The return value depends on your TIMEZONE parameter.

Example (I live in Germany, the actual time zone 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 displays something in the configured time zone. So the offset you get with EXTRACT(TIMEZONE FROM ...) depends on your TIMEZONE parameter. The time zone that was indicated on INSERT is lost because it is not worth saving. The fact is that everything is correct, and this should not depend on the installation of TIMEZONE . PostgreSQL does this very well.

Answer 2

"PostgreSQL does it very well."

I really like PostgreSQL, but it doesn’t do it well in this particular function. Time zone is not only offset by GMT. The time zone is closely linked to political rules that imply daylight saving time. Since there are many time zones with the same offset and different daylight saving time rules - when PG forgets about the original time zone, it actually loses information.

Personally, I keep the original time zone separate for dates that matter in the form "America / New_York." If someone has a better solution - it is welcome.

Answer 3

In Postgres, the original input time interval is lost even if you store the date and time value in a time stamp with the time zone data type.

Inconvenience:
Tell me if I have datetime data (along with timezone information) when people use their facebook app all over the world. Now I save these values ​​in a timestamp with the time zone data type. On one random day, I want to see how much people use their FB application in the morning compared to night, and if the same trend is observed in different countries. But wait, I no longer have time zone information. Sitting in DC, what I see is how activity was launched around the world when it was 10 AM EST.

Advantage:
If you compare two data sources with the time stamp of the same time zone, but one was stored as what the local clock showed, and the other store, converting it to UTC. Here you can simply save the timestamp in the type timestamptz and indicate which time zone it belongs to, and then you can easily compare them.
For example, photographs Activity in the PST time zone was recorded at 2014-10-19 10:23:54 . Now two separate data sources store it separately. Datasource1 saved it as 2004-10-19 10:23:54 PST , Datasource2 saved it as 2014-10-19 18:23:54 UTC . If they are stored in the timestamptz type, it will show you the same time when you do

 SELECT datasource1.time, datasource2.time 

Answer 4

Since the timestampz is restored momentarily during the ZULU / GMT time, which never changes its offset (since it is a reference), there is no need to record the time zone. You only need to add / subtract the offset to the offset time zone GEOPOLITICAL in PAST, PRESENT or FUTURE.

You need to know the exact GEOPOLITICAL TIME in effect at the place to which the current time applies for the purposes of PAST and PRESENT.

For future cases over time, this may become more problematic. It should work anyway. Think of the sunset. If in some place on Earth there is sunset @ midnight time ZULU (somewhere above the Atlantic Ocean or Northern Canada to Alaska in the winter in the Northern Hemisphere), and this time is considered equal to 8:00 (offset -4: 00) in this place on as soon as you record it in the system, and write it as “winter-date-in-the-future 8:00 PM”, it will be recorded as 24:00 GMT in the database.

Now, this place on earth receives hair up its * ss and previews with its nose according to a schedule related to geographic time, and calls their time zone “+11: 55”. Therefore, for them, when at midnight in England (midnight at midnight), WANT to call it 11:55 in the morning, it is their choice. When any computer wants to display this date in the future for this location (i.e. This geopolitical time zone), they will call it 11:55 in the morning, even if the sun is setting. And, of course, THIS WILL BE AN ADULTS DAY of the day you planned it :-) Their problem.