关于在数据库中保存日期时间和时区信息有很多问题,但总体上还有更多问题。在这里,我想谈一谈具体案例。
系统规格
数据库中需要涵盖业务规则
ORDR-13432-Year-Month-Day
)来计算。目前,精确的计算并不重要,这取决于租户本地日期时间,这一点很重要我们最初的想法
方法1
每个租户可以节省本地租户的日期时间,但是这样我们就遇到了查询问题:
SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
这是有问题的,因为OrderDateTime
在此查询中,根据租户意味着不同的时刻。当然,此查询可能包括连接到Tenants
表以获取本地datetime偏移量,然后OrderDateTime
该偏移量将即时计算以进行调整。有可能,但不确定是否是一个好方法?
方法2
让我们举一个极端的例子;假设租户比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
。
在这种情况下,在数据库中创建Order时,我们应该获得UTC日期时间,租户偏移并基于重新计算的租户本地时间编译OrderNumber,但仍将UTC中的DateTime列保存。
问题
[更新]
根据Gerard Ashton和Hugo的评论:
关于细节,最初的问题尚不清楚,租户是否可以更改时区,以及如果政治权威更改时区属性或某些领土的时区会发生什么。当然,这是极其重要的,但这并不是这个问题的中心。我们可以在另一个问题中解决这个问题。
为了这个问题,让我们假设房客不会改变位置。该位置的时区属性或时区本身可能会更改,并且这些更改将在系统中与该问题分开处理。
雨果的答案基本上是正确的,但我要补充一些要点:
当您存储客户的时区时,请勿存储数字偏移量。正如其他人指出的那样,与UTC的偏移量仅是一个时间点,并且由于DST和其他原因而可以轻松更改。相反,您应该将时区标识符(最好是IANA时区标识符)存储为字符串,例如"America/Los_Angeles"
。在时区标签Wiki中了解更多信息。
您的OrderDateTime
字段应绝对代表UTC时间。但是,根据数据库平台的不同,您可以选择几种存储方式。
例如,如果使用Microsoft SQL Server,一种好的方法是将本地时间存储在datetimeoffset
列中,以保留与UTC的偏移量。请注意,您在该列上创建的任何索引都将基于UTC等效项,因此在进行范围查询时,您将获得良好的查询性能。
如果使用其他数据库平台,则可能希望将UTC值存储在timestamp
字段中。某些数据库也具有timestamp with time zone
,但要理解,这并不意味着它存储时区或偏移量,而只是意味着它可以在存储和检索值时隐式地为您进行转换。如果您打算始终代表UTC,则通常timestamp
(无时区)或datetime
更合适。
由于上述两种方法都将存储UTC时间,因此,您还需要考虑如何执行需要本地时间值索引的操作。例如,您可能需要根据用户所在时区的日期创建每日报告。为此,您需要按本地日期分组。如果尝试在查询时根据UTC值计算该值,则最终将扫描整个表。
解决此问题的一种好方法是为本地date
(或datetime
根据您的需要,甚至本地,而不是 a datetimeoffset
或timestamp
)创建一个单独的列。这可能是您单独填充的完全隔离的列,也可能是基于其他列的计算/计算列。在索引中使用此列,以便您可以按本地日期进行过滤或分组。
如果您采用计算列方法,则需要知道如何在数据库中的时区之间进行转换。某些数据库具有convert_tz
内置的功能,可以理解IANA时区标识符。
如果您使用的是Microsoft SQL Server,则可以AT TIME ZONE
在SQL 2016和Azure SQL DB中使用新功能,但这仅适用于Microsoft时区标识符。要使用IANA时区标识符,您需要第三方解决方案,例如我的SQL Server时区支持项目。
在查询时,请避免使用该BETWEEN
语句。它是完全包容的。整个日期都可以,但是如果您有时间,最好进行半开范围查询,例如:
... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2
例如,如果@t1
是今天的开始,那@t2
将是明天的开始。
关于注释中讨论的场景,其中用户的时区已更改:
如果选择计算数据库中的本地日期,则您唯一需要担心的情况是,某个地点或企业切换时区时不会发生“区域拆分”。区域划分是指引入新的时区标识符,该标识符涵盖更改的区域,包括其旧规则和新规则。
例如,在撰写本文时,IANA tzdb中添加的最新区域是America/Punta_Arenas
,这是智利南部决定停留在UTC-3时智利的其余部分(America/Santiago
)回到UTC-4 时划分的区域。在DST结束时。
但是,如果两个时区边界上的一个次要地区决定更改他们遵循的哪一侧,并且不保证进行时区分割,那么您可能会对他们的旧数据使用新时区的规则。
如果您单独存储本地日期(在应用程序中计算,而不是在数据库中计算),则不会有任何问题。用户将其时区更改为新时区,所有旧数据仍保持不变,并且新数据与新时区一起存储。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句