Thread: TimeZone List
I've been trying to sort out the answer to this question for a while now, I've received different answers from different places. I'm looking for a definitive non-volatile list of timezones for use in a web application. I can't use the OS's time zone list, as changing OSes may cause some listed timezones to disappear or change name, causing havoc for any entries in the DB referring to those zones. Is relying on zone.tab to be a non-volatile list of timezones appropriate for this purpose? Or is there an ISO listing of the various time zones? I would prefer to use a list that matched the PostgreSQL DB's internal timezone strings so they could easily be referenced without any further work in the app. Thanks - Naz.
Naz Gassiep wrote:
> I've been trying to sort out the answer to this question for a while
> now, I've received different answers from different places.
>
> I'm looking for a definitive non-volatile list of timezones for use in a
> web application. I can't use the OS's time zone list, as changing OSes
> may cause some listed timezones to disappear or change name, causing
> havoc for any entries in the DB referring to those zones.
As of 8.2 you can do this:
alvherre=# select * from pg_timezone_names ;
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
Africa/Algiers | CET | 01:00:00 | f
Africa/Luanda | WAT | 01:00:00 | f
Africa/Porto-Novo | WAT | 01:00:00 | f
Africa/Gaborone | CAT | 02:00:00 | f
Africa/Ouagadougou | GMT | 00:00:00 | f
Africa/Bujumbura | CAT | 02:00:00 | f
Africa/Douala | WAT | 01:00:00 | f
Africa/Bangui | WAT | 01:00:00 | f
Africa/Ndjamena | WAT | 01:00:00 | f
Africa/Kinshasa | WAT | 01:00:00 | f
Africa/Lubumbashi | CAT | 02:00:00 | f
Africa/Brazzaville | WAT | 01:00:00 | f
Africa/Abidjan | GMT | 00:00:00 | f
Africa/Djibouti | EAT | 03:00:00 | f
Africa/Cairo | EEST | 03:00:00 | t
Africa/Malabo | WAT | 01:00:00 | f
Africa/Asmera | EAT | 03:00:00 | f
...
alvherre=# select * from pg_timezone_abbrevs ;
abbrev | utc_offset | is_dst
--------+------------+--------
ACSST | 10:30:00 | t
ACST | -04:00:00 | t
ACT | -05:00:00 | f
ADT | -03:00:00 | t
AESST | 11:00:00 | t
AEST | 10:00:00 | f
AFT | 04:30:00 | f
AKDT | -08:00:00 | t
AKST | -09:00:00 | f
ALMST | 07:00:00 | t
...
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > alvherre=# select * from pg_timezone_names ; > name | abbrev | utc_offset | is_dst > ----------------------------------+--------+------------+-------- > Africa/Algiers | CET | 01:00:00 | f > Africa/Luanda | WAT | 01:00:00 | f > Africa/Porto-Novo | WAT | 01:00:00 | f > Africa/Gaborone | CAT | 02:00:00 | f > Africa/Ouagadougou | GMT | 00:00:00 | f > Africa/Bujumbura | CAT | 02:00:00 | f > Africa/Douala | WAT | 01:00:00 | f > Africa/Bangui | WAT | 01:00:00 | f > Africa/Ndjamena | WAT | 01:00:00 | f > Africa/Kinshasa | WAT | 01:00:00 | f > Africa/Lubumbashi | CAT | 02:00:00 | f > Africa/Brazzaville | WAT | 01:00:00 | f > Africa/Abidjan | GMT | 00:00:00 | f > Africa/Djibouti | EAT | 03:00:00 | f > Africa/Cairo | EEST | 03:00:00 | t > Africa/Malabo | WAT | 01:00:00 | f > Africa/Asmera | EAT | 03:00:00 | f > ... > > alvherre=# select * from pg_timezone_abbrevs ; > abbrev | utc_offset | is_dst > --------+------------+-------- > ACSST | 10:30:00 | t > ACST | -04:00:00 | t > ACT | -05:00:00 | f > ADT | -03:00:00 | t > AESST | 11:00:00 | t > AEST | 10:00:00 | f > AFT | 04:30:00 | f > AKDT | -08:00:00 | t > AKST | -09:00:00 | f > ALMST | 07:00:00 | t > ... > Ok, that's kinda cool. But can I trust those names to not change from version to version? And how are conflicts in the abbreviations handled? (for example, EST is a valid time zone for the US and Australia). Also, whats with the "Etc/GMT+x" timezones? I assume they are just standard integer offset values? I'd probably filter them out in the app using "WHERE substring(name FROM 0 FOR 3) <> "Etc" are there any caveats for doing this? Thanks, - Naz
Naz Gassiep <naz@mira.net> writes:
> Ok, that's kinda cool. But can I trust those names to not change from
> version to version?
No, you can't. The reason there is no "nonvolatile" list of timezones
is that timezones are subject to the whims of politicians, who can and
do change them at the drop of a hat. Read the historical comments in
the zic source files sometime...
regards, tom lane
Tom Lane wrote: > Naz Gassiep <naz@mira.net> writes: > >> Ok, that's kinda cool. But can I trust those names to not change from >> version to version? >> > > No, you can't. The reason there is no "nonvolatile" list of timezones > is that timezones are subject to the whims of politicians, who can and > do change them at the drop of a hat. Read the historical comments in > the zic source files sometime... As if war, famine, injustice and social inequality weren't enough, politicians also have to screw up time zone handling in my app. Damn politicians. - Naz.
> No, you can't. The reason there is no "nonvolatile" list of timezones > is that timezones are subject to the whims of politicians, who can and > do change them at the drop of a hat. Read the historical comments in > the zic source files sometime... It used to be that "fully one third of timezones in Postgres are for Australia" (according to some Postgres documentation I read in 2003 in the old seperate Thomas Lockhart manuals) Under this metric select name from pg_timezone_names where name like 'Aus%'; we're only 23 out of 557. I think we still have the most number of different timezones though, we throw in some odd half-hour difference ones and we have a couple of offshore islands in all kinds of places. We certainly punch above our weight in world affairs when it comes to Timezones. Also we're responsible for more of Antarctica than any other country. Stuart. Sydney, Australia.