Re: TimeZone List

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