Fix ‘time zone … not recognized’ in PostgreSQL

by

If you’re getting an error that reads something like ‘time zone “US/New_York” not recognized‘ in PostgreSQL while using a function such as date_add(), date_subtract() or date_trunc(), it’s probably because you’re specifying an unsupported time zone.

If you specify a time zone, it must be one that’s recognised by the system.

To fix this issue, either provide a recognised time zone or omit the time zone argument altogether (assuming this is a valid option for the situation).

Example of Error

Here’s an example of code that produces the error:

SELECT date_add(
    '2023-09-23 07:30:00+07'::timestamptz,
    '3 day'::interval,
    'Thailand/Bangkok'
    );

Result:

ERROR:  time zone "Thailand/Bangkok" not recognized

The error message is pretty self explanatory – the time zone that I specified is not recognised by the system.

Solution

The solution is to use a time zone that’s recognised by the system:

SELECT date_add(
    '2023-09-23 07:30:00+07'::timestamptz,
    '3 day'::interval,
    'Asia/Bangkok'
    );

Result:

        date_add        
------------------------
 2023-09-26 07:30:00+07

This time it ran without error. That’s because I changed the time zone to Asia/Bangkok (which is a recognised time zone) instead of Thailand/Bangkok (which is not a recognised time zone).

Supported Time Zones

PostgreSQL allows us to specify time zones in three different forms:

  • A full time zone name, for example America/New_York. The recognised time zone names are listed in the pg_timezone_names view.
  • A time zone abbreviation, for example PST. This option defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition rules as well. The recognised abbreviations are listed in the pg_timezone_abbrevs view.
  • PostgreSQL also accepts POSIX-style time zone specifications. This option is not normally preferable to using a named time zone, but it may be necessary if no suitable IANA time zone entry is available.

See Return a List of Timezones Supported by PostgreSQL for examples of the first two options.