Sometimes we need to specify a time zone when constructing timestamp values in DuckDB. But we may not always know the exact value to use for the time zone.

Fortunately, we can use the pg_timezone_names() table function to get a list of available timezones in DuckDB.

Example

Here’s an example of querying the pg_timezone_names() table function to get a list of available timezones:

SELECT *
FROM pg_timezone_names()
ORDER BY name;

Result:

+--------------------+----------------+------------+--------+
|        name        |     abbrev     | utc_offset | is_dst |
+--------------------+----------------+------------+--------+
| ACT                | ACT            | 09:30:00   | false  |
| AET                | AET            | 11:00:00   | true   |
| AGT                | AGT            | -03:00:00  | false  |
| ART                | ART            | 02:00:00   | false  |
| AST                | AST            | -09:00:00  | false  |
| Africa/Abidjan     | Iceland        | 00:00:00   | false  |
| Africa/Accra       | Iceland        | 00:00:00   | false  |
| Africa/Addis_Ababa | EAT            | 03:00:00   | false  |
| Africa/Algiers     | Africa/Algiers | 01:00:00   | false  |
| Africa/Asmara      | EAT            | 03:00:00   | false  |
...

The query returns over 600 rows, and so I’ve presented just the top ten rows here.

You can use the values when specifying time zones. For example:

SELECT TIMESTAMP '2035-07-20 12:35:45+08' AT TIME ZONE 'Africa/Asmara';

Result:

+-----------------------------------------------------------------------------+
| main.timezone('Africa/Asmara', CAST('2035-07-20 12:35:45+08' AS TIMESTAMP)) |
+-----------------------------------------------------------------------------+
| 2035-07-20 11:35:45+10                                                      |
+-----------------------------------------------------------------------------+