系统规格
>我们有一个Orders系统数据库
>这是一个多租户系统,租户可以使用任意时区(每个租户是任意的,但是单个时区,一次保存在租户表中,永不改变)
DB中需要涵盖业务规则
>当租户将订单放入系统时,订单编号将根据其本地日期时间计算(它不是字面上的数字,而是某种类型的标识符,如ORDR-13432-年 – 月 – 日).精确计算目前并不重要,重要的是它取决于租户的本地日期时间
>我们还希望能够在系统级别上选择所有订单,放置在某些UTC日期时间之间,而不管租户(对于一般系统统计/报告)
我们最初的想法
>我们最初的想法是在整个数据库中保存UTC日期时间,当然,保持租户时区相对于UTC的偏移量,并且使用DB的应用程序总是将日期时间转换为UTC,以便DB本身始终使用UTC.
方法1
>保存本地租户的日期时间对于每个租户来说都不错,但是我们遇到的问题包括：
SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
这是有问题的,因为此查询中的OrderDateTime意味着基于租户的不同时刻.当然,此查询可能包括连接到Tenants表以获取本地日期时间偏移量,然后可以动态计算OrderDateTime以进行调整.这是可能的,但不确定这是否是一个好方法呢？
方法2
>另一方面,当保存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,但仍然以UTC格式保存DateTime列.
问题
>处理这种情况的首选方法是什么？
>保存UTC日期时间是否有一个很好的解决方案,因为系统级报告对我们来说非常好？
>如果使用保存UTC,是方法2)处理这些情况的好方法还是有一些更好/推荐的方法？
[UPDATE]
根据Gerard Ashton和Hugo的评论：
如果租户可以改变时区,那么如果政治当局改变时区属性或某个时区的时区会发生什么,那么初步问题就细节而言并不明确.
当然,这是非常重要的,但它不是这个问题的核心.我们可以在另一个问题中解决这个问题.
为了这个问题,让我们假设租户不会改变位置.该位置的时区属性或时区本身可能会发生变化,这些更改将在系统中与此问题分开处理.
解决方法@H_404_50@
雨果的回答大多是正确的,但我会补充一些要点：
>当您存储客户的时区时,请勿存储数字偏移.正如其他人所指出的那样,UTC的偏移仅适用于单个时间点,并且可以很容易地因DST和其他原因而改变.相反,您应该存储时区标识符,最好是IANA时区标识符作为字符串,例如“America / Los_Angeles”.阅读更多the timezone tag wiki.
>您的OrderDateTime字段应绝对代表UTC的时间.但是,根据您的数据库平台,您有多种选择来存储它.
>例如,如果使用Microsoft sql Server,一种好方法是将本地时间存储在datetimeoffset列中,该列保留UTC的偏移量.请注意,您在该列上创建的任何索引都将基于UTC等效项,因此在进行范围查询时,您将获得良好的查询性能.
>如果使用其他数据库平台,您可能希望将UTC值存储在时间戳字段中.某些数据库也有时区的时间戳,但要明白它并不意味着它存储时区或偏移量,它只是意味着它可以在您存储和检索值时隐式地为您进行转换.如果您打算始终表示UTC,那么通常时间戳(没有时区)或只是日期时间更合适.
>由于上述任一方法都将存储UTC时间,因此您还需要考虑如何执行需要本地时间值索引的操作.例如,您可能需要根据用户时区的日期创建每日报告.为此,您需要按本地日期分组.如果您尝试在查询时根据UTC值计算,则最终会扫描整个表格.
处理此问题的一个好方法是为本地日期创建一个单独的列(或者甚至根据您的需要创建本地日期时间,但不是datetimeoffset或timestamp).这可以是单独填充的完全隔离的列,也可以是基于其他列的计算/计算列.在索引中使用此列,以便按本地日期过滤或分组.
>如果您使用计算列方法,则需要知道如何在数据库中的时区之间进行转换.某些数据库具有内置的convert_tz功能,可以理解IANA时区标识符.
如果您使用的是Microsoft sql Server,则可以在sql 2016和Azure sql DB中使用新的AT TIME ZONE功能,但这仅适用于Microsoft时区标识符.要使用IANA时区标识符,您需要第三方解决方案,例如我的SQL Server Time Zone Support项目.
>在查询时,请避免使用BETWEEN语句.它是完全包容的.它适用于整个日期,但是当你有时间参与时,你最好做一个半开放范围的查询,例如：
... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2
例如,如果@ t1是今天的开始,@ t2将是明天的开始.
关于用户时区已更改的注释中讨论的方案：
>如果您选择计算数据库中的本地日期,则唯一需要担心的情况是位置或业务是否在没有“区域拆分”的情况下切换时区.区域拆分是指引入新时区标识符时覆盖已更改的区域,包括旧规则和新规则.
例如,在撰写本文时添加到IANA tzdb的最新区域是America / Punta_Arenas,这是智利南部决定在智利其他地区(美国/圣地亚哥)停留在UTC-3时的区域分割在夏令时结束时回到UTC-4.
但是,如果两个时区边界上的次要位置决定更改它们所遵循的哪一侧,并且不保证区域拆分,那么您可能会使用其新时区的规则来对抗其旧数据.>如果您单独存储本地日期(在应用程序中计算,而不是数据库),那么您将没有任何问题.用户将其时区更改为新时区,所有旧数据仍然完好无损,新数据将与新时区一起存储.
>当您存储客户的时区时,请勿存储数字偏移.正如其他人所指出的那样,UTC的偏移仅适用于单个时间点,并且可以很容易地因DST和其他原因而改变.相反,您应该存储时区标识符,最好是IANA时区标识符作为字符串,例如“America / Los_Angeles”.阅读更多the timezone tag wiki.
>您的OrderDateTime字段应绝对代表UTC的时间.但是,根据您的数据库平台,您有多种选择来存储它.
>例如,如果使用Microsoft sql Server,一种好方法是将本地时间存储在datetimeoffset列中,该列保留UTC的偏移量.请注意,您在该列上创建的任何索引都将基于UTC等效项,因此在进行范围查询时,您将获得良好的查询性能.
>如果使用其他数据库平台,您可能希望将UTC值存储在时间戳字段中.某些数据库也有时区的时间戳,但要明白它并不意味着它存储时区或偏移量,它只是意味着它可以在您存储和检索值时隐式地为您进行转换.如果您打算始终表示UTC,那么通常时间戳(没有时区)或只是日期时间更合适.
>由于上述任一方法都将存储UTC时间,因此您还需要考虑如何执行需要本地时间值索引的操作.例如,您可能需要根据用户时区的日期创建每日报告.为此,您需要按本地日期分组.如果您尝试在查询时根据UTC值计算,则最终会扫描整个表格.
处理此问题的一个好方法是为本地日期创建一个单独的列(或者甚至根据您的需要创建本地日期时间,但不是datetimeoffset或timestamp).这可以是单独填充的完全隔离的列,也可以是基于其他列的计算/计算列.在索引中使用此列,以便按本地日期过滤或分组.
>如果您使用计算列方法,则需要知道如何在数据库中的时区之间进行转换.某些数据库具有内置的convert_tz功能,可以理解IANA时区标识符.
如果您使用的是Microsoft sql Server,则可以在sql 2016和Azure sql DB中使用新的AT TIME ZONE功能,但这仅适用于Microsoft时区标识符.要使用IANA时区标识符,您需要第三方解决方案,例如我的SQL Server Time Zone Support项目.
>在查询时,请避免使用BETWEEN语句.它是完全包容的.它适用于整个日期,但是当你有时间参与时,你最好做一个半开放范围的查询,例如：
... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2
例如,如果@ t1是今天的开始,@ t2将是明天的开始.
关于用户时区已更改的注释中讨论的方案：
>如果您选择计算数据库中的本地日期,则唯一需要担心的情况是位置或业务是否在没有“区域拆分”的情况下切换时区.区域拆分是指引入新时区标识符时覆盖已更改的区域,包括旧规则和新规则.
例如,在撰写本文时添加到IANA tzdb的最新区域是America / Punta_Arenas,这是智利南部决定在智利其他地区(美国/圣地亚哥)停留在UTC-3时的区域分割在夏令时结束时回到UTC-4.
但是,如果两个时区边界上的次要位置决定更改它们所遵循的哪一侧,并且不保证区域拆分,那么您可能会使用其新时区的规则来对抗其旧数据.>如果您单独存储本地日期(在应用程序中计算,而不是数据库),那么您将没有任何问题.用户将其时区更改为新时区,所有旧数据仍然完好无损,新数据将与新时区一起存储.
