什么是适合存储时区的数据类型？

Question

我考虑只使用格式为“+hh:mm”（或“-hh:mm”）的字符串。这样做既必要又足够吗？

注意：我不需要存储日期或时间，只需存储时区。

- jl6

Answer 1

很遗憾，PostgreSQL没有提供时区数据类型，所以你应该使用text。

乍一看，interval似乎是一个合理的选择，并且对于某些用途而言确实是适当的。然而，它未考虑夏令时，也未考虑在相同UTC偏移的不同地区存在不同的DST规则。

从UTC偏移到时区并不存在1:1的映射关系。

例如，Australia/Sydney（新南威尔士州）的时区是UTC+10（EST），夏令时期间为UTC+11（EDT）。是的，这和美国使用的缩写EST相同；时区缩写在tzdata数据库中是非唯一的，这就是为什么Pg有timezone_abbreviations设置。更糟糕的是，布里斯班（昆士兰州）几乎在同一经度上，处于UTC+10 EST...但是没有夏令时，因此有时在新南威尔士州的DST期间，它会偏移-1。

（更新：最近，澳大利亚采用了前缀A，因此它使用AEST作为东部各州的TZ缩写，但EST和WST仍然广泛使用）。

很令人困惑吧？

如果你只需要存储一个UTC偏移，那么interval是适当的。如果你想存储一个时区，则将其存储为text。目前验证和转换为时区偏移是一件麻烦事，但至少可以处理夏令时。

- Craig Ringer

---

所有时区的规范文本字符串（Australia/Sydney）是否有链接？ - odigity

如果您想要优化性能和节省空间（并减少可移植性/灵活性），则时区数据库+枚举将是不错的选择。 - Hendy Irawan

Answer 2

在理想的世界中，您可以使用外键引用到一组已知的时区。通过视图和域，您可以实现接近于此的功能。

David E. Wheeler的wiki tip创建了一个经过验证为时区有效性的域。

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN
 PERFORM now() AT TIME ZONE tz;
 RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN
 RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );

有一个已知时区列表是很有用的，这样你就可以不需要域名，只在包含已知时区名称的一个表中强制执行约束（从视图pg_timezone_names获取），避免在其他地方公开域名的需要：

CREATE TABLE tzone
(
  tzone_name text PRIMARY KEY (tzone_name) CHECK (is_timezone(tzone_name))
);

INSERT INTO tzone (tzone_name)
SELECT name FROM pg_timezone_names;

然后，您可以通过外键强制执行正确性:

CREATE TABLE myTable (
...
tzone TEXT REFERENCES tzone(tzone_name)
);

- beldaz

---

建议的方法不够有竞争力，PG也能处理错误的TZ值。下面是一个TZ值不正确的示例，PG可以成功处理，但在所有方面都是错误的。 SELECT '2022-01-01 00:00' AT TIME ZONE 'Europe/Warsaw +02:00' 结果： 2021-12-31 19:00:00 - vlasov

@vlasov 当然可以，但这也是我建议从pg_timezone_names构建允许的时区列表的部分原因。 - beldaz

Answer 3

在Postgres中，您已经可以将任何TIMESTAMP或TIMESTAMPTZ转换为命名时区，因此您无需从表中查找值。 您可以直接在检查约束中使用此表达式，因此您也无需创建函数：

CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    name TEXT,
    timezone TEXT NOT NULL CHECK (now() AT TIME ZONE timezone IS NOT NULL)
);

如果您尝试插入一个不包含有效时区的值，您将得到一个相当用户友好的错误：

INSERT INTO locations (name, timezone) VALUES ('foo', 'Adelaide/Australia');
ERROR:  time zone "Adelaide/Australia" not recognized

根据您的要求，你可能需要错误信息以普通约束违规的格式呈现，但在许多情况下这样做已足够。

如果您使用的是提供下拉框中可选时区列表的 Web 框架，则此验证应该就足够了，那么您的检查约束只是一个备份。

- Matthew Schinckel

---

我不确定你的评论是否适用于我的答案，或者说适用于这个问题。这是关于存储时区，而不是存储带有时区的时间戳。 - Matthew Schinckel

Answer 4

"

"+hh:mm"和"-hh:mm"不是时区，而是UTC偏移量。一个好的保存格式是使用带有分钟偏移量的带符号整数。你也可以使用interval等方法，但这只能在PostgreSQL中直接进行日期计算时才有用，如在查询中等。通常情况下，你会在另一种语言中进行这些计算，那么它取决于该语言是否良好地支持interval类型并且是否具有良好的日期/时间库。但将整数转换为某种类似于interval的类型，例如Python的timedelta，应该很简单，因此我个人只会将其存储为整数。

时区有名称，虽然没有标准化的名称，但在“tz”或“zoneinfo”数据库中有一个事实上的标准，即像“Europe/Paris”、“Americas/New_York”或“US/Pacific”这样的名称。这些应该存储为字符串。

Windows使用完全不同的名称，例如“Romance time”（别问为什么）。你也可以将它们存储为字符串，但我建议避免使用它们，因为这些名称在Windows之外并不使用，并且这些名称毫无意义。此外，Windows的翻译版本倾向于使用已翻译的时区名称，这使情况变得更糟。

类似于“PDT”和“EST”的缩写不能用作时区名称，因为它们不是唯一的。有四个（我想，或者是五个？）不同的时区都叫“CST”，所以这是不能用的。

简而言之：对于时区，请将名称存储为字符串。对于UTC偏移量，请将偏移量以分钟为单位存储为带符号整数。

"

- Lennart Regebro

---

所有时区的规范文本字符串（Australia/Sydney）是否有链接？ - odigity

Answer 5

可能是间隔时间

postgres=# 选择间隔时间 '01:30';
 间隔时间 
----------
 01:30:00
（1 行）

postgres=# 选择间隔时间 '-01:30';
 间隔时间  
-----------
 -01:30:00
（1 行）

- Pavel Stehule