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

What is an appropriate data type to store a timezone?

提问by jl6

I'm thinking of simply using a string in the format "+hh:mm" (or "-hh:mm"). Is this both necessary and sufficient?

我正在考虑简单地使用格式为“+hh:mm”（或“-hh:mm”）的字符串。这是否既必要又充分？

Note: I don't need to store the date or the time, just the timezone.

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

回答by Craig Ringer

Unfortunately PostgreSQL doesn't offer a time zone data type, so you should probably use text.

不幸的是，PostgreSQL 不提供时区数据类型，因此您可能应该使用text.

intervalseems like a logical option at first glance, and it isappropriate for some uses. However, it fails to consider daylight savings time, nor does it consider the fact that different regions in the same UTC offset have different DST rules.

interval好像乍一看合理的选择，它是适用于某些用途。但是，它没有考虑夏令时，也没有考虑同一UTC偏移量的不同地区有不同的夏令时规则。

There is not a 1:1 mapping from UTC offset back to time zone.

没有从 UTC 偏移量回时区的 1:1 映射。

For example, the time zone for Australia/Sydney(New South Wales) is UTC+10(EST), or UTC+11(EDT) during daylight savings time. Yes, that's the same acronym ESTthat the USA uses; time zone acronyms are non-unique in the tzdata database, which is why Pg has the timezone_abbreviationssetting. Worse, Brisbane (Queensland) is at almost the same longditude and is in UTC+10 EST... but doesn't have daylight savings, so sometime it's at a -1offset to New South Wales during NSW's DST.

例如，夏令时期间Australia/Sydney（新南威尔士州）的时区是UTC+10( EST) 或UTC+11( EDT)。是的，这EST与美国使用的首字母缩写词相同；时区首字母缩略词在 tzdata 数据库中不是唯一的，这就是 Pg 具有该timezone_abbreviations设置的原因。更糟糕的是，布里斯班（昆士兰州）的经度几乎相同，并且处于UTC+10 EST……但没有夏令时，因此有时-1在新南威尔士州的夏令时期间会与新南威尔士州发生偏移。

(Update: More recently Australia adopted an Aprefix, so it uses AESTas its eastern states TZ acronym, but ESTand WSTremain in common use).

（更新：最近澳大利亚采用的A前缀，所以它使用AEST它的东部各州TZ缩写，但EST并WST留在共同使用）。

Confusing much?

很混乱吗？

If all you need to store is a UTC offsetthen an intervalis appropriate. If you want to store a time zone, store it as text. It's a pain to validate and to convert to a time zone offset at the moment, but at least it copes with DST.

如果您需要存储的只是一个UTC 偏移量，那么 aninterval是合适的。如果要存储时区，请将其存储为text. 目前验证并转换为时区偏移量很痛苦，但至少它可以应对 DST。

回答by Lennart Regebro

"+hh:mm" and "-hh:mm" are not time zones, they are UTC offsets. A good format to save those are as a signed integer with the offset in minutes. You can also use things like intervalbut that will only help you if you want to do date calculations directly in PostgreSQL, like in a query, etc. Usually though you do these calculations in another language, and then it depends on that language if it supports the intervaltype well and has a good date/time library or not. But converting an integer into some sort of interval-like type, like Pythons timedeltashould be trivial, so I would personally just store it as an integer.

"+hh:mm" 和 "-hh:mm" 不是时区，它们是 UTC 偏移量。将这些保存为带有以分钟为单位的偏移量的带符号整数的好格式。您也可以使用类似interval但仅当您想直接在 PostgreSQL 中进行日期计算时才对您有所帮助，例如在查询等中。通常虽然您使用另一种语言进行这些计算，然后它取决于该语言是否支持该interval型好，具有良好的日期/时间库与否。但是将整数转换为某种类似interval类型的类型，比如 Pythontimedelta应该是微不足道的，所以我个人只是将它存储为一个整数。

Time zones have names, and although there are no standardized names for the time zones there is one de facto standard in the "tz" or "zoneinfo" database, and that's names like "Europe/Paris", "Americas/New_York" or "US/Pacific". Those should be stored as strings.

时区有名称，尽管时区没有标准化名称，但在“tz”或“zoneinfo”数据库中有一个事实上的标准，例如“Europe/Paris”、“Americas/New_York”或“美国/太平洋”。这些应该存储为字符串。

Windows uses completely different names, such as "Romance time" (don't ask). You can store them as well as strings, but I would avoid it, these names aren't used outside Windows, and the names make no sense. Besides, translated versions of windows tend to use translated names for these timezones, making it even worse.

Windows 使用完全不同的名称，例如“浪漫时光”（不要问）。您可以存储它们以及字符串，但我会避免这样做，这些名称不在 Windows 之外使用，并且名称没有意义。此外，Windows 的翻译版本倾向于使用这些时区的翻译名称，这让情况变得更糟。

Abbreviations like "PDT" and "EST" are not usable as time zone names, because they are not unique. There is four (I think, or was it five?) different time zones all called "CST", so that's not usable.

“PDT”和“EST”等缩写不能用作时区名称，因为它们不是唯一的。有四个（我认为，还是五个？）不同的时区都称为“CST”，所以这是不可用的。

In short: For time zones, store the name as a string. For UTC offsets, store the offset in minutes as a signed integer.

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

回答by beldaz

In an ideal world you could have a foreign key to a set of known timezones. You can do something close to this with views and domains.

在理想的世界中，您可以拥有一组已知时区的外键。您可以使用视图和域来做一些接近于此的事情。

This wiki tipby David E. Wheleer creates a domain that is tested for its validity as a timezone:

David E. Wheleer 的这个wiki 技巧创建了一个域，用于测试其作为时区的有效性：

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

It's useful to have a list of known timezones, in which case you could dispense with the domain and just enforce the constraint in the one table containing the known timezone names (obtained from the view pg_timezone_names), avoiding the need to expose the domain elsewhere:

拥有一个已知时区列表很有用，在这种情况下，您可以省去域，只需在包含已知时区名称（从视图中获取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;

Then you can enforce correctness through foreign keys:

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

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

回答by Matthew Schinckel

In postgres, you can already cast any TIMESTAMPor TIMESTAMPTZto or from a named timezone, so you don't need to look up values from a table. You can use this expression directly in a check constraint, so you don't need to create a function for this either:

在 postgres 中，您已经可以将 anyTIMESTAMP或TIMESTAMPTZto 或 from a named timezone 进行转换，因此您无需从表中查找值。您可以直接在检查约束中使用此表达式，因此您也不需要为此创建函数：

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

If you try to insert a value that does not contain a valid timezone, you'll get an error that is actually rather user friendly:

如果您尝试插入一个不包含有效时区的值，您将收到一个实际上对用户友好的错误：

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

Depending upon your requirements, you might needthe error to be in the format that a normal constraint violation would provide you, however in many cases this will suffice.

根据您的要求，您可能需要错误的格式符合正常的约束违规，但在许多情况下，这已经足够了。

If you are using a web framework that provides you with a list of timezones that you can have in a dropdown box, then this validation should be sufficient, and then your check constraint is just a backup.

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

回答by Pavel Stehule

maybe interval

也许间隔

postgres=# select interval '01:30';
 interval 
----------
 01:30:00
(1 row)

postgres=# select interval '-01:30';
 interval  
-----------
 -01:30:00
(1 row)