How to convert standard format time zone into tz database time format?

Question

I have an api that is returning timezones like "Pacific Standard Time".

I want to use that format in AT TIME ZONE. When I try

SELECT '2020-11-01T18:46:00'::timestamptz AT TIME ZONE 'Pacific Standard Time'

I get ERROR: time zone "Pacific Standard Time" not recognized

I need "Pacific Standard Time" to look like "US/Pacific" or "America/Los_Angeles"

I have seen the pg_timezone_names table built into Postgres but I cannot find 'Pacific Stanard Time' in them. Any advice on converting this into a type that can be used with Postgres?

Answer 1

Per our discussion in the question chat, let's assume your input values are all Windows time zone identifiers.

The source of truth for conversion between Windows and IANA identifiers is the windowsZones.xml file, part of the Unicode CLDR project.

You can parse the necessary data from this file using the xml features of Postgres, such as xmlparse and xmltable.

CREATE TABLE zone_mapping AS
SELECT xmltable.*
FROM XMLPARSE (DOCUMENT '...put xml here...')
AS data,
xmltable('//mapZone[@territory = ''001'']'
  PASSING data
  COLUMNS
    windows_zone varchar PATH '@other' not null,
    iana_zone varchar PATH '@type' not null
);

You can then query it, or join to it in your own queries, etc.

select * from zone_mapping;

Results will include the value you're looking for:

windows_zone              iana_zone
-----------------------------------------------
...
Pacific Standard Time     America/Los_Angeles
...

Working fiddle here (click Run at top).

Note that by filtering to the "001" territory (called the "Golden Zone"), the mapping table will work in the Windows to IANA direction only. If you need to go the other direction, then it's much more complex because not only do you have to take the other entries into consideration, but you also have to expand to all IANA aliases (links) using other data sources.

Also note when you paste your xml in, you'll need to replace any ' characters with '' for the query. There is only one in the file presently.

Answer 2

To help in cases like this I have written a Postgresql function that uses windowsZones.xml as a source of data as advised by Matt Johnson-Pint above. It converts between Windows and IANA time zone names and has this function prototype:

FUNCTION timezone_names(windows_tz text DEFAULT NULL, iana_tz text DEFAULT NULL, territory text DEFAULT NULL)
RETURNS TABLE(windows_timezone text, liana_timezone text, territory_abbrev text)

Examples:

-- List of all Windows and IANA time zones and territory codes (as to store in a table)
select * from timezone_names();

-- Data for Bulgaria by IANA time zone name
select * from timezone_names(iana_tz => 'Europe/Sofia');

-- Data for Bulgaria by Windows time zone name and territory
select * from timezone_names(windows_tz => 'FLE Standard Time', territory => 'BG');

In order to force a single-line result for a Windows or IANA time zone name supply
territory => '001' as an argument.

• The function is a bit too long to be put in the answer. It can be found here.
• In case when time zones change (the EU is about to make such a change sometime soon) the XML document must be extracted again, single quotes doubled and then replaced in the function body (lines 6 - 800).