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