处理 UTC、时区并使用 LocalTime 对其进行 GROUP BY

Question

我被困住了。需要帮助。

我将 UTC 日期存储在数据库中。

示例行：

GenerateAt:

• 2011-06-08 23:30
• 2011-06-09 03:30
• 2011-06-09 15:30

我的用户的当地时间是 -2 小时（中欧）。当我想要 09 开始的行时，我有 3 行。

问题在于用于报告目的的 GROUP BY day 。我有 1 代表 08 年，2 代表 09 年，但对于我的当地时间来说，情况并非如此。

我到处都看到：“以 UTC 格式存储数据”。如何正确地做到这一点？

更新1：
对于数据访问，我使用 NHibernate，并且我更喜欢独立于数据库引擎的解决方案。所以，我正在寻找诸如日期/时间维度表（或类似的东西）之类的解决方案。

我的数据表具有如下列：

• GenerateAt（日期时间）
• GenerateAt_Year（int）
• GenerateAt_Month（int）
• GenerateAt_Day（int）
• GenerateAt_Hour（int）

感谢我可以轻松地按以下方式分组：年，年+月，年+月+日，年+月+日+小时。不幸的是，这是 UTC。 :(

如何重构这个解决方案来处理用户时区？

Answer 1

您可以创建该表的视图，通过应用 DATEADD 函数提供所需中欧时区的日期时间值。

因此，如果您的表列是：id, other_id, evt_time

，那么 select 语句（用于视图定义）将是：

SELECT id, other_id, evt_time, DATEADD( hh, -2, evt_time ) AS evt_time_ce
  FROM MyTable

那么您可以使用该视图并将 GROUP_BY 应用于evt_time_ce 列

Answer 2

我一般也有类似的问题，但我的时差是8小时。

我使用 dateadd(hour,8, [Timestamp]) 选择当地时间，然后使用 dateadd(hour, -8, @dateFrom) 在 WHERE 子句中 - 这也适用于 GROUP BY。

例如：

DECLARE @dateFrom datetime, @dateUntil datetime
SET @dateFrom =  '2011-06-20 00:00:02.000'
SET @dateUntil = '2011-06-22 10:00:00.000'

SELECT TOP 100
    dateadd(hour, 8, [Timestamp]) LocalTime, 
        *
FROM [Log] L (nolock)
WHERE L.[Timestamp] BETWEEN dateadd(hour, -8, @dateFrom) AND dateadd(hour, -8, @dateUntil)
ORDER BY LogID DESC

Answer 3

SQL Server 2016 引入了 AT TIME ZONE 可以帮助解决此问题，包括处理夏令时。

AT TIME ZONE 允许您在 Windows 注册表中列出的时区之间转换日期。您可以通过运行简单的 SELECT 来查看可用的时区：

select * from sys.time_zone_info

下面的 SQL 将 UTC 存储的日期转换为另一个时区的日期

GeneratedAt
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central Europe Standard Time'

因此，根据本地时区按天分组的 SQL 可能如下所示：

SELECT DATEADD(DAY,0, DATEDIFF(DAY, 0, GeneratedAt
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central Europe Standard Time'))
FROM YourTable
GROUP BY DATEADD(DAY,0, DATEDIFF(DAY, 0, GeneratedAt
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central Europe Standard Time'))

如果您想了解更多内容，这里有一篇关于该主题的好文章。