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

达涅耶拉:

关于在数据库中保存日期时间和时区信息有很多问题,但总体上还有更多问题。在这里,我想谈一谈具体案例。

系统规格

  • 我们有一个订单系统数据库
  • 这是一个多租户系统,租户可以使用任意时区(它是任意的,但每个租户使用一个时区,一次保存在“租户”表中,永不更改)

数据库中需要涵盖业务规则

  • 当租户将订单下放到系统中时,订单号将根据其本地日期时间(不是字面上的数字而是某种标识符,例如ORDR-13432-Year-Month-Day)来计算。目前,精确的计算并不重要,这取决于租户本地日期时间,这一点很重要
  • 我们还希望能够选择系统时间在某些UTC日期时间之间放置的所有定单,而与租户无关(用于常规系统统计/报告)

我们最初的想法

  • 我们最初的想法是节省整个数据库中的UTC日期时间,并且当然要使租户时区相对于UTC偏移,并使消耗DB的应用程序始终将日期时间转换为UTC,以便DB本身始终使用UTC进行操作。

方法1

  • 每个租户可以节省本地租户的日期时间,但是这样我们就遇到了查询问题:

    SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
    

这是有问题的,因为OrderDateTime在此查询中,根据租户意味着不同的时刻。当然,此查询可能包括连接到Tenants表以获取本地datetime偏移量,然后OrderDateTime偏移量将即时计算以进行调整。有可能,但不确定是否是一个好方法?

方法2

  • 另一方面,在保存UTC日期时间时,当我们进行OrderNumber的计算时,由于UTC中的日期/月份/年份可能与本地日期时间中的日期/月份不同

让我们举一个极端的例子;假设租户比UTC早6个小时,而他的本地日期是2017-01-01 02:00UTC将是2016-12-31 20:00此时下的订单应获得OrderNumber,'ORDR-13432-2017-1-1'但如果保存UTC,则将获得ORDR-13432-2016-12-31

在这种情况下,在数据库中创建Order时,我们应该获得UTC日期时间,租户偏移并基于重新计算的租户本地时间编译OrderNumber,但仍将UTC中的DateTime列保存。

问题

  1. 处理这种情况的首选方式是什么?
  2. 是否有一种节省UTC日期时间的好方法,因为由于系统级的报告,这对我们来说很不错?
  3. 如果与节省UTC一起使用,方法2)是处理这些情况的好方法还是有一些更好/建议的方法?

[更新]

根据Gerard Ashton和Hugo的评论:

关于细节,最初的问题尚不清楚,租户是否可以更改时区,以及如果政治权威更改时区属性或某些领土的时区会发生什么。当然,这是极其重要的,但这并不是这个问题的中心。我们可以在另一个问题中解决这个问题。

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

马特·约翰逊·品脱(Matt Johnson-Pint):

雨果的答案基本上是正确的,但我要补充一些要点:

  • 当您存储客户的时区时,请勿存储数字偏移量。正如其他人指出的那样,与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 datetimeoffsettimestamp创建一个单独的列这可能是您单独填充的完全隔离的列,也可能是基于其他列的计算/计算列。在索引中使用此列,以便您可以按本地日期进行过滤或分组。

  • 如果您采用计算列方法,则需要知道如何在数据库中的时区之间进行转换。某些数据库具有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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

在PostgreSQL数据库中保存日期和时间的最佳方法

EF-将日期时间和特定时区保存在数据库中,但将其读取为UTC

在数据库后端存储时间和日期

是否可以检索数据库中保存了时间的时区?

在数据库中存储时间(仅时间而非日期)的最佳方法

将日期和时间保存到数据库

日期未在数据库中保存正确的值

如何解析日期和时间取决于时区?

如何在数据库中存储日期和时间(用户执行操作时存储日期)

使用JAVA在数据库中存储日期/时间信息?

如何通过SQL查询在数据库中仅保存时间而不保存日期

PHP oop在数据库中插入日期/时间

从数据库时间戳显示时间和日期

数据的 Firebase 数据库分页取决于时间戳

数据库返回了无效的日期时间值。是否已安装数据库和pytz的时区定义?

在FullCalendar的日视图中拖放事件时,开始日期时间和结束日期时间未记录在数据库中

将日期和时间从VB.Net保存到mysql数据库日期?

在Firebase数据库中保存日期

在数据库中搜索模型时,由于日期时间格式导致测试失败

如何使用SQL在数据库中插入当前日期和时间?

FullCalendar-在数据库中保存时区的建议

Laravel 5.6将日期时间保存到数据库

在数据库中按Emp_ID和信用日期插入“最小日志时间”和“最大日志时间”

数据库中的数据时,MySQL和PHP日期时间未保持活动状态

如何根据joomla配置中的时区在joomla数据库中保存当前时间?

在应用程序中可以确定日期,但可在数据库中保存-2小时

在DoExpressCheckout上单击“购买”后,将日期时间保存到数据库时出错

在Android的SQLite数据库中存储日期和时间

使用php将日期和时间输入数据库