当数据依赖于日期时间时,在数据库中保存日期时间和时区信息的最佳做法

前端之家收集整理的这篇文章主要介绍了当数据依赖于日期时间时,在数据库中保存日期时间和时区信息的最佳做法前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
有很多关于保存日期和时间的问题.数据库中的时区信息,但在整体水平上更多.在这里,我想谈谈具体案例.

系统规格

>我们有一个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的评论

如果租户可以改变时区,那么如果政治当局改变时区属性或某个时区的时区会发生什么,那么初步问题就细节而言并不明确.
当然,这是非常重要的,但它不是这个问题的核心.我们可以在另一个问题中解决这个问题.

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

解决方法

雨果的回答大多是正确的,但我会补充一些要点:

>当您存储客户的时区时,请勿存储数字偏移.正如其他人所指出的那样,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.

但是,如果两个时区边界上的次要位置决定更改它们所遵循的哪一侧,并且不保证区域拆分,那么您可能会使用其新时区的规则来对抗其旧数据.>如果您单独存储本地日期(在应用程序中计算,而不是数据库),那么您将没有任何问题.用户将其时区更改为新时区,所有旧数据仍然完好无损,新数据将与新时区一起存储.

原文链接:https://www.f2er.com/mssql/83721.html

猜你在找的MsSQL相关文章