在 PostgreSQL 应用中处理不同的时区

Question

我们有一个为多个配送中心提供服务的系统。配送中心是全国各地的物理空间。一个客户可能会有多个配送中心。现在我们正在扩展到更多的地方，我们将不得不面对不同时区的问题。同一客户可能也有位于不同时区的中心。

许多事件可以使用我们的系统在客户中心创建并保存（带有日期和小时）。对于同一客户的不同时区，理想的行为方式如下：

考虑一个发生在时区A的中午的事件。如果时区B的另一个分销中心的主管去检查此事件，则应根据事件最初创建的时区（包括夏令时变化（如果存在））查看该事件的日期和时间。

这是因为重要的是知道事件是否在事件时区的中午创建的。对于主管来说，不重要的是当事件被创建时，在他所在的时区中是否为下午2点。

我们使用PostgreSQL作为我们的数据库，并且我看到存在两种不同类型的时间戳。TIMESTAMP和TIMESTAMPTZ。我们的所有数据库都只使用类型TIMESTAMP。

另一个可能（或可能不）发生的情况是分销中心地理位置的更改。这可能会影响其时区的更改。

我已经做了一些研究，并发现更“正确”的方法（至少看起来是这样）是在DISTRIBUTION_CENTER表中保存每个中心的时区。将我们数据库中的所有类型从TIMESTAMP更改为TIMESTAMPTZ，并且在保存时间戳的每个事件的插入中，我们应该使用创建事件的中心的时区来保存事件时区的偏移量（TIMESTAMPTZ仅保存时区的偏移量，而不是时区本身）。

我并不完全相信这是处理不同时区的正确（或最佳）方法。因为我从未实现过这样的事情，所以我无法说。

如果我必须遵循此方法，则必须更改数据库中所有列类型从TIMESTAMP到TIMESTAMPTZ。所有依赖某种方式的那些列的视图也必须重新创建，因为我们将更改列类型。我还必须更改处理该列的所有查询，以使用AT TIMEZONE应用中心时区。

数据库现在设置了America/Sao_Paulo时区，我担心在更改TIMESTAMPTZ列类型时会做错事情。这种更改可能会破坏数据一致性吗？我应该首先更改列类型还是更改数据库时区为UTC？

我描述的解决方案是最好的方法吗？

该方法是否正确处理夏令时更改？

额外信息：我们的服务器使用Java（Jersey）。

- Luiz

Answer 1

这些问题在Stack Overflow和其姊妹网站https://dba.stackexchange.com/上已经被多次提及。下次在发帖前请认真搜索。以下是简要回顾。

首先，您必须理解offset-from-UTC和time zone之间的区别。偏移量仅是与UTC相差的小时数、分钟数和秒数。时区是特定地区人们使用的偏移量过去、现在和未来变化的历史记录。因此，使用时区总是优先于仅使用偏移量。

有两种不同的类型可用于保存时间戳：TIMESTAMP和TIMESTAMPTZ

并非完全如此。由SQL标准定义的实际类型为TIMESTAMP WITH TIME ZONE和TIMESTAMP WITHOUT TIME ZONE。其他名称是Postgres特定的同义词。出于清晰起见，建议使用标准名称。日期时间处理已经令人困惑了，不需要额外记住结尾处的z的歧义性。

SQL规范几乎没有涉及日期时间处理的主题。因此，在数据库实现之间行为差异很大。

Postgres的工作方式实际上非常简单。

对于类型为TIMESTAMP WITH TIME ZONE的列，任何具有UTC偏移或时区的输入都会自动调整为UTC。在调整后，原始值的偏移/区域信息被丢弃。 "with time zone"实际上意味着“考虑到传入数据的时区”，而不是“存储带有时区”。如果您必须知道原始偏移/区域，请在单独的列中自行存储。我建议使用ISO 8601标准格式的偏移或时区的正确名称将其存储为文本。如果输入缺少任何区域/偏移指示器，则应用会话的当前默认时区，然后将其调整为UTC-正如我模糊地记得的那样; 您永远不应该传递缺少区域/偏移的输入！

对于没有时区的TIMESTAMP WITHOUT TIME ZONE列，忽略任何输入中的区域/偏移信息（如果有）。同样，在检索此值时，它没有区域/偏移。这种类型没有区域/偏移概念。当您的意图是存储时间轴上的时刻，不要使用此类型。此类型用于关于26-27个小时范围内潜在时刻的模糊想法，例如“圣诞节从2018年12月25日午夜后开始”。除非您附加“在日本”，“在印度”或“在法国”（从而创建另一种类型的值，TIMESTAMP WITH TIME ZONE），否则这样的语句没有真正的意义。此类型还用于预约未来几周以上的时间，当时政客可能会突然更改其地区的偏移量时（他们意外地经常这样做，并且没有事先警告）。

注意：令人困惑的是，某些工具或驱动程序可能会将会话当前默认时区应用于任一类型的值。这包括pgAdmin。在我看来，这是一个可怕的反功能。出于善意，但是这样一个工具/驱动程序坐在您和Postgres之间，不应该注入其关于传输数据的“意见”。这样做会产生这样的错觉：检索到的数据携带了数据库内部的该区域，而事实上恰恰相反（实际上携带UTC或无区域/偏移量）。如果您的工具进行了这样的调整，则很可能由您的Postgres会话中的区域/偏移设置控制，如此处所述。

在处理日期时间的最佳实践中，应该使用UTC来思考、工作、存储、记录和交换数据。将其他区域视为该主题的简单变化。只有在业务逻辑或向用户展示时需要调整到时区。忘记你自己的地方时区。在桌子上放置第二个设置为UTC的时钟-说真的。

数据库现在设置了America/Sao_Paulo时区

服务器操作系统的默认时区应该是无关紧要的。作为程序员，永远不要依赖这样的默认值，因为它已经超出了您的控制，并且很容易更改。

在Java中，JVM具有自己的当前默认时区，与主机操作系统分开。JVM的当前默认值可以由JVM内部任何应用程序的任何线程中的任何代码在运行时随时更改。因此，永远不要依赖于当前默认值。始终通过传递可选参数明确指定所需/期望的时区。

如果时区B的另一个分销中心的主管检查此事件，则应看到如下内容：正如上文所述，数据库方面应该使用UTC。将其调整为用户期望的时区是用户界面任务，而不是数据库任务。就像国际化一样，您可以在数据库中存储某种键查找值，以在用户界面侧本地化为某种人类语言。

"TIMESTAMPTZ"仅保存时区的偏移量，而不是时区本身。

不，不正确。正如上文所述，“带时区的时间戳”类型在调整为UTC进行存储后会丢弃偏移/区域信息。没有偏移量，没有区域，只有UTC时刻存储在列中 - 基本上是自纪元参考以来微秒计数。

在我们的数据库中将所有类型从“TIMESTAMP”更改为“TIMESTAMPTZ”，并在保存时间戳的每个事件的插入中，应使用创建事件的中心的时区来保存事件的时区偏移量。

如果您要说您已经记录了来自各种时区的日期时间值到没有时区的“TIMESTAMP”列中，则会出现糟糕的混乱。您无法可靠地清理它，不能完全确定精度，因为您实际上不知道传递给数据库的输入的原始意图是什么区域/偏移量。您可以猜测存储数据的原始意图，但永远不可能确定。

请向您的老板和利益相关者解释，这不是您造成的混乱。请解释说，设置此数据库和应用程序的人相当于将各种货币（如日元、加元、英镑和欧元）存储在其中，而没有记录每个金额所属的货币。

如果要猜测，您需要知道可能使用的时区名称。

在Java中，仅使用内置于Java 8及更高版本的java.time类。旧的日期时间类非常混乱，现在已经过时，被JSR 310中定义的java.time所取代。

确定您可能的时区。

ZoneId zoneSaoPaulo = ZoneId.of( "America/Sao_Paulo" ) ;
ZoneId zoneLisbon = ZoneId.of( "Europe/Lisbon" ) ;
ZoneId zoneKolkata = ZoneId.of( "Asia/Kolkata" ) ;

提取日期时间值作为LocalDateTime，这是一个缺乏任何时区/偏移概念的Java类的日期时间值。使用JDBC 4.2及更高版本，您可以直接与数据库交换java.time对象。

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

也许枚举类型是表示您的配送中心的适当方式。这假设列表在运行时不需要更改。

public enum DistributionCenter {
    // List the constants to be constructed automatically when this class loads.
    SAOPAULO( ZoneId.of( "America/Sao_Paulo" ) ) ,
    LISBON( ZoneId.of( "Europe/Lisbon" ) ) ,
    KOLKATA( ZoneId.of( "Asia/Kolkata" ) ) 

    final public ZoneId zoneId ;  // Make public, or add a getter method to access private member.

    // Add constructor taking the passed `ZoneId` and storing in the variable.
}

应用时区，生成一个ZonedDateTime对象。现在我们有了一个实际的时刻，时间线上的一个特定点。

DistributionCenter dc = … ;
ZonedDateTime zdt = ldt.atZone( dc.zoneId ) ;

将该值调整为UTC值。同一时刻，时间轴上的同一点，不同的挂钟时间。在您清楚理解这个概念之前，请勿继续进行项目。

Instant类表示时间轴上以UTC为基准的瞬间，分辨率为纳秒（最多九位小数）。

Instant instant = zdt.toInstant() ;

你应该能够将你的ZonedDateTime对象传递给JDBC驱动程序，以进行调整为UTC。我只是想强调一点，我们最终在Postgres存储中得到的是UTC值。此外，我自己也会转换为Instant以便于调试 - 记住：UTC是“唯一真正的时间”。现在我们已确定了一个实际时刻，我们可以将其存储在数据库中。

myPreparedStatement.setObject( … , instant ) ;

请看这里：

请注意，所有这些代码都不依赖于您的服务器主机操作系统、您的Postgres集群或您的JVM的当前默认时区。

我必须将我们数据库中的所有列类型从TIMESTAMP更改为TIMESTAMPTZ

是的。记录实际时刻、历史片段的数据不应该存储在TIMESTAMP WITHOUT TIME ZONE中。一些天真的程序员/DBA希望使用这种数据类型可以免除处理时区问题的麻烦。但事实上，这是一种“现在付款，或以后付款”的情况。不幸的是，你被困在了他们糟糕选择的泥潭中。

你可能可以通过Postgres过程完成同样的工作。Postgres确实比大多数数据库有更好的日期时间处理支持。然而，在日期时间处理方面，没有什么能打败java.time类库。并且，个人而言，我宁愿在Java中调试和练习这个特定的任务。

配送中心地理位置发生变化了

这很令人困惑，也不明智。企业确实应该将新位置标识为一个新中心，而不是相同的中心。如果你无法说服管理层这样做，我会在你的数据库和后台应用程序中进行操作。

---

关于java.time

java.time框架是内置于Java 8及更高版本中的。这些类取代了老旧的legacy日期时间类，如java.util.Date、Calendar和SimpleDateFormat。

要了解更多信息，请参见Oracle教程。并在Stack Overflow上搜索许多示例和解释。规范为JSR 310。

Joda-Time 项目现在处于 维护模式，建议迁移到 java.time 类。

您可以直接使用 java.time 对象与数据库交换。使用符合 JDBC 4.2 或更高版本的 JDBC 驱动程序。无需字符串，无需 java.sql.* 类。Hibernate 5 和 JPA 2.2 支持 java.time。

如何获取 java.time 类？

• Java SE 8, Java SE 9, Java SE 10, Java SE 11及更高版本 - 标准Java API的一部分，带有一个捆绑实现。
• Java 9 带来了一些小的功能和修复。
• Java SE 6和Java SE 7
  • 大多数java.time功能在ThreeTen-Backport中被移植到Java 6和7中。
• Android
  • 较新版本的Android（26+）捆绑了java.time类的实现。
  • 对于早期的Android（<26），API desugaring过程会带来一个不是原始内置于Android中的java.time子集功能。
    • 如果解糖不能提供所需功能，则ThreeTenABP项目将ThreeTen-Backport（上述已提到）适配到Android中。请参阅如何使用ThreeTenABP…。

- Basil Bourque

---

很棒的写作。TimeZone配置参数仅从服务器时区初始化，之后服务器时区不起任何作用。TimeZone不是数据库时区，它是会话时区的默认值。PostgreSQL没有数据库时区的概念。 - Laurenz Albe

谢谢你的回答！到目前为止，我们只在 TIMESTAMP WITHOUT TIMEZONE 列中保存了一个时区的时间戳（幸好）。如果一个数据中心发生地理位置和时区变化，未来创建的事件应该考虑新的时区，对于已经存储的内容：没有任何更改。如果因为先前的数据而无法创建新的数据中心，则我无法创建一个新数据中心。监管人员必须能够查看迁移前创建的数据以及新数据（每个数据尊重其各自不同的时区，假设数据中心的更改也会更改其时区）。 - Luiz

很遗憾，由于截止日期的原因，我无法更改为新的Java 8 API。我们的服务器仍将使用旧的java.util.Date和java.sql.Date。我们的网站和Android应用程序都使用序列化的java.util.Date对象接收时间戳，没有任何有关TZ的信息。理论上，数据应该已经准备好显示（只需要格式）。由于这是一个要求（不信任客户端tz应用更改），我不被允许更改。那么，在哪里以及如何应用TZ更改到DB时间戳（考虑DST更改）？ - Luiz

Answer 2

阅读 Basil 的出色回答以了解这些概念。

你应该转换为 timestamp with time zone，实际上是一个绝对时间戳（轻微违反了 SQL 标准的意图）。

你需要考虑的一件事是，如果记录事件的中心的时区发生变化，事件的时区是否应该更改。如果不是，你将不得不为每个中心保留时区历史记录，或者（更好的方法）在创建每个事件时将时区存储在其中。

- Laurenz Albe

---

我明白了。所以对于数据库中的每个timestamp with time zone列，我还需要一个额外的列来保存时区。当查询该数据时，我将能够使用AT TIME ZONE，对吗？ - Luiz

还有一件事情让我感到困扰：如果在应用夏令时时保存了一个事件，然后当夏令时关闭时（假设我们使用“AT TIME ZONE”查询数据），主管查看此事件，他将会看到错误的时间？ - Luiz

如果您将时区与事件一起存储，则会在该时区中获得结果。无论何时观看，该值都不会改变，除非时区法律发生历史性变化（这种情况确实存在）。 - Laurenz Albe

我明白了。但是，如果一个事件在巴西12月2日下午2点（夏令时开启时）保存，并且监管人员在3月份（夏令时关闭时）打开该事件，那么时间会显示为下午1点？比12月份事件实际发生的时间早一个小时？很抱歉，所有这些时区问题肯定会让我发疯。:s - Luiz

不，即使在三月份使用 AT TIME ZONE 'America/Sao_Paulo'，它仍然会显示下午2点。因为在那个时区的那个时间确实是下午2点。别担心。 - Laurenz Albe