Return a List of Timezones Supported by PostgreSQL

Posted on by Ian

When working with PostgreSQL, you may occasionally find yourself in the situation where you need a list of timezones that Postgres recognises.

For example, you could be trying to set the timezone for your current session, or you could be using one of the datetime functions that allow you to manipulate the timezone.

Below are two views and two functions that return a list of timezones.

The pg_timezone_abbrevs View

The pg_timezone_abbrevs view provides a list of timezone abbreviations that are currently recognised by the datetime functions.

Here’s what the top 10 rows look like:

SELECT * 
FROM pg_timezone_abbrevs
LIMIT 10;

Result:

abbrev  | utc_offset | is_dst
--------+------------+--------
ACDT    | 10:30:00   | t
ACSST   | 10:30:00   | t
ACST    | 09:30:00   | f
ACT     | -05:00:00  | f
ACWST   | 08:45:00   | f
ADT     | -03:00:00  | t
AEDT    | 11:00:00   | t
AESST   | 11:00:00   | t
AEST    | 10:00:00   | f
AFT     | 04:30:00   | f

The is_dst column indicates whether or not this is a daylight-savings abbreviation.

Note that the contents of this view change when the timezone_abbreviations run-time parameter is modified.

Also note that the Postgres documentation states:

While most timezone abbreviations represent fixed offsets from UTC, there are some that have historically varied in value (see Section B.4 for more information). In such cases this view presents their current meaning.

The pg_timezone_abbrevs() Function

You can alternatively use the pg_timezone_abbrevs() function to return the results as a SETOF.

SELECT pg_timezone_abbrevs()
LIMIT 10;

Result:

pg_timezone_abbrevs
-------------------
(ACDT,10:30:00,t)
(ACSST,10:30:00,t)
(ACST,09:30:00,f)
(ACT,-05:00:00,f)
(ACWST,08:45:00,f)
(ADT,-03:00:00,t)
(AEDT,11:00:00,t)
(AESST,11:00:00,t)
(AEST,10:00:00,f)
(AFT,04:30:00,f)

You can also use the following syntax to return the results in separate columns if required.

SELECT * 
FROM pg_timezone_abbrevs()
LIMIT 10;

Result:

abbrev  | utc_offset | is_dst
--------+------------+--------
ACDT    | 10:30:00   | t
ACSST   | 10:30:00   | t
ACST    | 09:30:00   | f
ACT     | -05:00:00  | f
ACWST   | 08:45:00   | f
ADT     | -03:00:00  | t
AEDT    | 11:00:00   | t
AESST   | 11:00:00   | t
AEST    | 10:00:00   | f
AFT     | 04:30:00   | f

The pg_timezone_names View

The pg_timezone_names view provides a list of timezone names that are recognized by SET TIMEZONE, along with their associated abbreviations, UTC offsets, and daylight-savings status.

Here’s what the top 10 rows look like:

SELECT * 
FROM pg_timezone_names
LIMIT 10;

Result:

       name              | abbrev | utc_offset | is_dst 
------------------+--------+------------+--------
 Indian/Mauritius  | +04    | 04:00:00   | f
 Indian/Chagos     | +06    | 06:00:00   | f
 Indian/Mayotte    | EAT    | 03:00:00    | f
 Indian/Christmas  | +07    | 07:00:00    | f
 Indian/Cocos      | +0630  | 06:30:00    | f
 Indian/Maldives   | +05    | 05:00:00    | f
 Indian/Comoro     | EAT    | 03:00:00    | f
 Indian/Reunion    | +04    | 04:00:00    | f
 Indian/Mahe       | +04    | 04:00:00    | f
 Indian/Kerguelen  | +05    | 05:00:00    | f

The is_dst column indicates whether or not the timezone is currently observing daylight savings.

For this view, the Postgres documentation states:

Unlike the abbreviations shown in pg_timezone_abbrevs, many of these names imply a set of daylight-savings transition date rules. Therefore, the associated information changes across local DST boundaries. The displayed information is computed based on the current value of CURRENT_TIMESTAMP.

The pg_timezone_names() Function

You can also use the pg_timezone_names() function to return the results as a SETOF.

SELECT pg_timezone_names()
LIMIT 10;

Result:

pg_timezone_names
---------------------------------
(Indian/Mauritius,+04,04:00:00,f)
(Indian/Chagos,+06,06:00:00,f)
(Indian/Mayotte,EAT,03:00:00,f)
(Indian/Christmas,+07,07:00:00,f)
(Indian/Cocos,+0630,06:30:00,f)
(Indian/Maldives,+05,05:00:00,f)
(Indian/Comoro,EAT,03:00:00,f)
(Indian/Reunion,+04,04:00:00,f)
(Indian/Mahe,+04,04:00:00,f)
(Indian/Kerguelen,+05,05:00:00,f)

You can also use the following syntax to return the results in separate columns.

SELECT * FROM pg_timezone_names()
LIMIT 10;

Result:

name              | abbrev | utc_offset | is_dst 
------------------+--------+------------+--------
Indian/Mauritius  | +04    | 04:00:00   | f
Indian/Chagos     | +06    | 06:00:00   | f
Indian/Mayotte    | EAT    | 03:00:00   | f
Indian/Christmas  | +07    | 07:00:00   | f
Indian/Cocos      | +0630  | 06:30:00   | f
Indian/Maldives   | +05    | 05:00:00   | f
Indian/Comoro     | EAT    | 03:00:00   | f
Indian/Reunion    | +04    | 04:00:00   | f
Indian/Mahe       | +04    | 04:00:00   | f
Indian/Kerguelen  | +05    | 05:00:00   | f