datetime - 当数据依赖于日期时间时在数据库中保存日期时间和时区信息的最佳实践

Question

关于在数据库中保存日期时间和时区信息有很多问题，但更多是在整体层面上。这里我想讲一个具体的案例。

系统规范

我们有一个订单系统数据库

它是一个 Multi-Tenancy 系统，租户可以使用任意时区(它是任意的，但每个租户只有一个时区，在租户表中保存一次，永不更改)

DB 中需要涵盖业务规则

当租户向系统下订单时，订单号根据本地日期时间计算 (它不是字面上的数字，而是某种标识符，如 ORDR-13432-Year-Month-Day )。目前精确计算并不重要，重要的是它取决于租户本地日期时间

我们也确实希望能够在系统级别上选择所有订单，这些订单位于某些 UTC 日期时间之间，而不考虑租户(用于一般系统统计/报告)

我们最初的想法

我们最初的想法是在整个 DB 中保存 UTC 日期时间，当然，保持租户时区相对于 UTC 的偏移量，并使使用 DB 的应用程序始终将日期时间转换为 UTC，以便 DB 本身始终使用 UTC 运行。

方法一

为每个租户保存本地租户日期时间会很好，但是我们遇到了以下查询问题:

SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2

这是有问题的，因为 OrderDateTime在这个查询中意味着不同的时刻，基于租户。当然，这个查询可能包括 join to Tenants表获取本地日期时间偏移量，然后计算 OrderDateTime随时进行调整。这是可能的，但不确定这是否是一个好方法？

方法二

另一方面，当保存 UTC 日期时间时，当我们计算 OrderNumber 时，因为 UTC 中的日/月/年可能与本地日期时间中的不同

举个极端的例子；假设租户比 UTC 早 6 小时，他的本地日期时间是 2017-01-01 02:00 .
UTC 将是 2016-12-31 20:00 .在那一刻下的订单应该得到 OrderNumber 'ORDR-13432-2017-1-1'但如果保存 UTC，它会得到 ORDR-13432-2016-12-31 .

在这种情况下，在 DB 中创建 Order 的那一刻，我们应该根据重新计算的租户本地时间获取 UTC 日期时间、租户偏移量并编译 OrderNumber，但仍将 DateTime 列保存在 UTC 中。

问题

处理这种情况的首选方法是什么？

是否有保存 UTC 日期时间的不错解决方案，因为由于系统级报告，这对我们来说非常好？

如果要保存 UTC，方法 2) 是处理这些情况的好方法还是有更好/推荐的方法？

[更新]

根据 Gerard Ashton 和 Hugo 的评论:

最初的问题不清楚租户是否可以更改时区以及如果政治当局更改时区属性或某些领土的时区会发生什么情况。
当然，这是极其重要的，但它不是这个问题的中心。我们可能会在一个单独的问题中解决这个问题。

为了这个问题，让我们假设租户不会改变位置。该位置的时区属性或时区本身可能会更改，这些更改将在系统中与此问题分开处理。

Answer 1

Hugo 的回答大部分是正确的，但我要补充几个要点:

当您存储客户的时区时，不要存储数字偏移量。正如其他人指出的那样，与 UTC 的偏移量仅适用于单个时间点，并且很容易因夏令时和其他原因而改变。相反，您应该存储时区标识符，最好将 IANA 时区标识符存储为字符串，例如 "America/Los_Angeles" .阅读更多 the timezone tag wiki .

您的 OrderDateTime字段应该绝对代表UTC时间。但是，根据您的数据库平台，您有多种存储方式可供选择。

例如，如果使用 Microsoft SQL Server，一个好的方法是将本地时间存储在 datetimeoffset 中。列，保留与 UTC 的偏移量。请注意，您在该列上创建的任何索引都将基于 UTC 等效项，因此在执行范围查询时您将获得良好的查询性能。

如果使用其他数据库平台，您可能希望将 UTC 值存储在 timestamp 中。 field 。有些数据库还有timestamp with time zone ，但要明白这并不意味着它存储时区或偏移量，它只是意味着它可以在您存储和检索值时隐式地为您进行转换。如果您打算始终表示 UTC，那么通常 timestamp (无时区)或只是 datetime更合适。

由于上述任一方法都将存储 UTC 时间，因此您还需要考虑如何执行需要本地时间值索引的操作。例如，您可能需要根据用户所在时区的日期创建每日报告。为此，您需要按本地日期分组。如果您尝试在查询时根据您的 UTC 值计算该值，您最终将扫描整个表。

处理这个问题的一个好方法是为本地 date 创建一个单独的列。 (或者甚至是本地 datetime 取决于您的需要，但不是 datetimeoffset 或 timestamp )。这可能是您单独填充的完全隔离的列，也可能是基于您的其他列的计算/计算列。在索引中使用此列，以便您可以按本地日期过滤或分组。

如果您采用计算列方法，您将需要知道如何在数据库中的时区之间进行转换。一些数据库有 convert_tz了解 IANA 时区标识符的内置函数。

如果您使用的是 Microsoft SQL Server，则可以使用新的 AT TIME ZONE SQL 2016 和 Azure SQL DB 中的函数，但仅适用于 Microsoft 时区标识符。要使用 IANA 时区标识符，您需要第三方解决方案，例如我的 SQL Server Time Zone Support项目。

在查询时，避免使用 BETWEEN陈述。它是完全包容的。它适用于整个日期，但是当您有时间参与时，最好进行半开放范围查询，例如:

... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2

例如，如果 @t1是今天的开始，@t2将是明天的开始。

关于评论中讨论的用户时区已更改的场景:

如果您选择在数据库中计算本地日期，您唯一需要担心的情​​况是位置或业务是否切换时区而不会发生“区域拆分”。区域拆分是指引入新的时区标识符，该标识符涵盖了更改的区域，包括其旧规则和新规则。

例如，在撰写本文时添加到 IANA tzdb 的最新区域是 America/Punta_Arenas ，当智利的其他地区 (America/Santiago) 在夏令时结束时回到 UTC-4 时，智利南部决定留在 UTC-3，这是一个区域 split 。

但是，如果两个时区边界上的一个小地方决定改变他们遵循的哪一边，并且不保证区域拆分，那么您可能会使用他们新时区的规则来处理他们的旧数据。

如果您单独存储本地日期(在应用程序中计算，而不是在数据库中)，那么您将没有问题。用户将他们的时区更改为新时区，所有旧数据仍然完好无损，新数据与新时区一起存储。