如何将标准格式时区转换为tz数据库时间格式

Question

我有一个api，它正在返回时区，比如"太平洋标准时间"；。

我想在AT TIME ZONE中使用这种格式。当我尝试时

选择"2020-11-01T18:46:00"：时区的时间戳"太平洋标准时间">

我得到错误：时区"；太平洋标准时间"；未识别

我需要"；太平洋标准时间"；看起来像"；美国/太平洋"；或"；America/Loss_Angeles"；

我看到了Postgres中内置的pg_timezone_names表，但在其中找不到"太平洋标准时间"。关于将其转换为可与Postgres一起使用的类型，有什么建议吗？

Answer 1

根据我们在问题聊天中的讨论，让我们假设您的输入值都是Windows时区标识符。

Windows和IANA标识符之间转换的真相来源于windowsZones.xml文件，它是UnicodeCLDR项目的一部分。

您可以使用Postgres的xml特性(如xmlparse和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
);

然后你可以查询它，或者在自己的查询中加入它，等等。

select * from zone_mapping;

结果将包括您正在寻找的价值：

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

在此处工作(单击顶部的Run(

注意，通过过滤到"0"；001〃；区域(称为"黄金地带"(，映射表将仅在Windows到IANA的方向上工作。如果你需要转向另一个方向，那么它会更加复杂，因为你不仅必须考虑其他条目，而且还必须使用其他数据源扩展到所有IANA别名(链接(。

还要注意，在粘贴xml时，需要将查询的任何'字符替换为''。目前文件中只有一个。

Answer 2

为了在这种情况下提供帮助，我编写了一个Postgresql函数，该函数使用windowsZones.xml作为数据源，正如Matt Johnson Pint在上面建议的那样。它在Windows和IANA时区名称之间进行转换，并具有以下功能原型：

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)

示例：

-- 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');

为了强制Windows或IANA时区名称的单行结果，请提供
territory => '001'作为参数。

• 函数有点太长，无法放入答案中。它可以在这里找到
• 如果时区发生变化(欧盟很快就会做出这样的改变(，则必须再次提取XML文档，将单引号加倍，然后在函数体中替换(第6-800行(