原文链接：https://www.cybertec-postgresql.com/en/time-zone-management-in-postgresql/
作者：Laurenz Albe
除了字符编码，时区是计算中最不受欢迎的话题之一。此外，PostgreSQL 对带时区的时间戳的实现有些令人惊讶。所以我认为写一篇关于时区管理的介绍和实际使用的建议可能是值得的。
时区和 SQL 标准
SQL 标准对指定时区的规定相当有限：
<timestamp string> ::= <quote> <unquoted timestamp string> <quote> <unquoted time string> ::= <time value> [ <time zone interval> ] <time zone interval> ::= <sign> <hours value> <colon> <minutes value>复制
关于时区，它有以下说法：
地球表面被划分为多个区域，称为时区，其中每个正确的时钟都显示相同的时间，称为本地时间。本地时间等于 UTC（协调世界时）加上时区位移，这是一个介于 INTERVAL ‘–14:00’ HOUR TO MINUTE 和 INTERVAL ‘+14:00’ HOUR TO MINUTE 之间的间隔值。时区位移在整个时区中是恒定的，并在夏令时开始和结束时发生变化（如果适用）。
换句话说，该标准只有时区管理规定来表示与 UTC 的偏移量。必须应对夏令时更改的用户或应用程序必须在适当的时间更改偏移量以反映这一点。显然，SQL 标准的这一部分并没有考虑太多的可用性考虑（或者在标准委员会中具有影响力的大型数据库供应商都没有更智能的实现）。
值得注意的是，SQL 标准似乎将时区偏移视为时间戳值的一部分，尽管在这一点上并不完全清楚。
PostgreSQL 中的时间戳数据类型
PostgreSQL 中的时间戳有两种数据类型：时间戳（也称为不带时区的时间戳）和带时区的时间戳（或简称 timestamptz）。 timestamp 忽略了时区的考虑，在这里与我们无关。两种数据类型的分辨率均为微秒（小数点后六位）。
带时区的时间戳在两个方面与 SQL 标准的想法不同：
- 它不存储时区。相反，时间戳存储为自 2000-01-01 UTC 午夜以来的秒数。
- 除了与 UTC 的偏移量之外，还有更多方法可以指定时区（稍后会详细介绍）。这使您可以有效地处理夏令时更改。
这两点都会让 PostgreSQL 用户感到困惑，但第一点比第二点多。实际上，“timestamp with time zone”这个名称并不是对 PostgreSQL 数据类型的准确描述，称其为“absolute timestamp”更合适：它代表一个固定的时间点，与时间无关事件发生的区域。
为了完整起见，让我评论一个不好的做法：由于我无法理解的原因，许多人将时间戳存储在数据库中，作为自“Unix 纪元”（UTC 午夜 1970 年 1 月 1 日午夜）以来的秒数。从数据库的角度来看，这样做没有任何好处，只有一些缺点：
- 存储在数据库中的值是肉眼无法理解的。
- 您正在失去日期时间算术的大部分功能，例如能够计算
current_timestamp - INTERVAL '1 month'。 - 由于前一点，您的 WHERE 条件往往更复杂，这对您的查询性能不利。
TimeZone 数据库参数
上一节中的观察让您想知道如何在 PostgreSQL 中使用带时区的时间戳。关键是数据库参数 TimeZone，它包含当前数据库会话中使用的时区。每当 PostgreSQL 将带有时区的时间戳转换为字符串时，它都会根据 TimeZone 的当前设置对其进行格式化，并显示与 UTC 的适当偏移量：
CREATE TABLE tstz ( id bigint PRIMARY KEY, t timestamp with time zone NOT NULL ); INSERT INTO tstz VALUES (1, '2022-04-01 12:00:00+02:00'); INSERT INTO tstz VALUES (2, '2022-04-01 12:00:00-02:00'); SET TimeZone = 'UTC'; TABLE tstz; id │ t ════╪════════════════════════ 1 │ 2022-04-01 10:00:00+00 2 │ 2022-04-01 14:00:00+00 (2 rows)复制
这里的重要方面是 TimeZone 不打算在数据库服务器上集中设置。相反，每个数据库会话都应该根据在客户端生效的时区覆盖参数。这样，每个人都以不同的方式看到相同的时间戳，并以正确的时区格式化。
时区管理：PostgreSQL 中的语法
除了 SQL 标准的有限规定之外，PostgreSQL 还允许通过三种方式指定时区。您可以将它们用作参数 TimeZone 或 AT TIME ZONE 构造的值，还可以在时间戳常量中指定时区。
IANA 时区名称
IANA 是一个以管理顶级域和 IP 地址范围而闻名的互联网组织，但他们也维护着一个时区定义数据库，供世界各地的许多软件组件使用。该数据库以其创始人的名字命名为“奥尔森数据库”。
IANA 时区名称的格式为“区域/位置”。 “区域”是大陆或海洋或特殊区域等，用于没有地理位置的“行政区域”。 “位置”是最重要的城市或其他小区域，代表其周围使用的时区。此类时区的示例如下：
America/New_York Europe/Vienna Europe/Paris Pacific/Pitcairn Etc/UTC复制
要获取 PostgreSQL 安装中可用的时区列表，请查看系统目录 pg_timezone_names。 PostgreSQL 维护自己的 IANA 时区数据库副本，但大多数操作系统也有一个副本。从源代码构建 PostgreSQL 时，您可以配置要使用的副本。要确定您的 PostgreSQL 二进制文件正在使用 IANA 时区数据库的哪个副本，请运行“pg_config --configure”：如果输出包含 --with-system-tzdata，则使用操作系统的副本。
请注意，其中许多时区（如欧洲/维也纳和欧洲/巴黎）当前是相同的。但是，保留不同的名称是有意义的，因为这些时区并不总是相同的，将来也不需要保持相同。
IANA 时区包括有关当前和历史时区以及夏令时定义的数据：
SET TimeZone = 'UTC'; SELECT TIMESTAMPTZ '2022-04-01 12:00:00 Europe/Vienna'; timestamptz ════════════════════════ 2022-04-01 10:00:00+00 (1 row) SELECT TIMESTAMPTZ '2022-03-01 12:00:00 Europe/Vienna'; timestamptz ════════════════════════ 2022-03-01 11:00:00+00 (1 row) SELECT TIMESTAMPTZ '1850-02-01 12:00:00 Europe/Vienna'; timestamptz ════════════════════════ 1850-02-01 10:54:39+00 (1 row)复制
第一个结果和第二个结果之间的变化是因为维也纳在 2022 年 3 月的最后一个星期日改为夏令时，最后一个奇怪的结果表明，在 1893 年之前，维也纳使用的是当地太阳时而不是时区偏移格林威治标准时间。
时区缩写
这些是众所周知的缩写，例如“太平洋标准时间”的 PST 或“中欧时间”的 CET。您可以在系统目录 pg_timezone_abbrevs 中找到您的 PostgreSQL 数据库理解的列表。
请注意，这些时区缩写有几个缺点：
它们只是与 UTC 的某个固定偏移量的缩写，不包含有关夏令时的任何信息。例如，“Central European Time”有 CET，“Central European Summer Time”有 CEST。
没有关于这些缩写的国际协议。例如，在我的 PostgreSQL 安装中，IST 的意思是“以色列标准时间”（在冬季使用，与 UTC 相差 2 小时），而对于爱尔兰女性来说，相同的缩写代表“爱尔兰夏令时间”，近 10 亿人会期望它为“印度标准时间”。
有一个简短的缩写很方便，出于上述原因，我建议避免使用 UTC 以外的时区缩写。
POSIX 风格的时区规范
您可以在 PostgreSQL 文档的附录 B 中找到对 POSIX 时区语法的描述。主要优点是 POSIX 时区允许您指定夏令时更改，因此它们可能对 IANA 时区数据库未涵盖的情况很有用，这种情况不太可能发生。
为了说明该格式的全部功能，以下时区描述了欧盟大多数国家目前使用的时区：
CET-1CEST,M3.5.0/2,M10.5.0/3复制
应理解如下：在冬季，有效的时区缩写为 CET，比 UTC 早一小时。夏令时的缩写为 CEST，从 3 月（第 3 个月）的最后一个（“第五”）星期日（第 0 天）凌晨 2 点开始，到 10 月的最后一个星期日凌晨 3 点结束。
POSIX 时区也存在一些问题：
- POSIX 时区中的偏移量与其他地方使用的时区偏移量符号相反。例如，“2022-04-01 12:00:00 UTC-2”与“2022-04-01 12:00:00+02”相同：UTC 以东两个小时。
- 更复杂的 POSIX 时区不能用于时间戳文字，因为它们会混淆时间戳解析器。
- 无法使用 POSIX 时区语法来表达历史时区更改。
特别是这些点中的第一点是一个常见的混淆来源。因此，最好也避免使用 POSIX 时区。
PostgreSQL 时区管理中的类型转换和转换
有两种方法可以在带时区的时间戳和时间戳之间进行转换：类型转换和表达式 AT TIME ZONE。
当数据在两种数据类型之间转换时，时间戳总是被解释为在 TimeZone 给定的时区中：
SET TimeZone = 'UTC'; SELECT CAST (TIMESTAMP '2022-04-01 12:00:00' AS timestamp with time zone); timestamptz ════════════════════════ 2022-04-01 12:00:00+00 (1 row) SELECT CAST (TIMESTAMP WITH TIME ZONE '2022-04-01 12:00:00+02' AS timestamp); timestamp ═════════════════════ 2022-04-01 10:00:00 (1 row)复制
表达式 AT TIME ZONE 也在两种数据类型之间进行转换：
当应用于带有时区的时间戳时，结果是一个时间戳，显示给定时区的挂钟将显示的内容：
SELECT TIMESTAMP WITH TIME ZONE '2022-04-01 12:00:00+00' AT TIME ZONE 'Europe/Vienna'; timezone ═════════════════════ 2022-04-01 14:00:00 (1 row)复制
在 UTC 时区的中午十二点，维也纳挂钟将显示下午 2 点。
当应用于时间戳时，AT TIME ZONE 的结果是带有时区的时间戳，表示在给定时区的挂钟上与给定时间戳相对应的绝对时间：
SELECT TIMESTAMP '2022-04-01 12:00:00' AT TIME ZONE 'Europe/Vienna'; timezone ════════════════════════ 2022-04-01 10:00:00+00 (1 row)复制
当维也纳时钟显示中午十二点时，就是世界标准时间上午 10 点。
如何编写时区感知应用程序
在应用程序中处理时区管理有两种好方法：
- 始终使用时间戳，存储 UTC 时间戳并让应用程序处理时区转换。
- 始终使用带时区的时间戳，在每个会话中正确设置 TimeZone 并让 PostgreSQL 处理时区转换。
- 不要尝试混合解决方案，它们可能会导致痛苦和困惑。
结论
尽管很容易与时区混淆，但如果您在任何地方都使用带时区的时间戳，坚持使用 IANA 时区名称并确保将 TimeZone 参数设置为客户端的时区，则可以避免大多数问题。然后 PostgreSQL 将为您完成所有繁重的工作。
