时间戳

首页 > 解决方案 > 使用UTC和时区查询时间范围内的记录

sql-server - 使用UTC和时区查询时间范围内的记录

问题描述

我正在寻找一种解决方案，以使用包含当前系统时间的开始和停止时间快速有效地搜索记录。我希望我能找到一个不需要 SQL Server 对存储在表中的数据格式执行数学运算或转换的解决方案。可以使用第二组值来计算夏令时，并且查询逻辑可以在执行之前确定美国夏令时规则是否有效。我的意图是在当前时间使用 UTC，以便逻辑可以移动到 Azure 或其他数据中心并且与位置无关。

我考虑过但发现不足的解决方案：

当前使用的解决方案使用 UTC 偏移量计算每条记录的当前时间，这需要 SQL Server 的工作量超过此操作所需的工作量。

我发现最好的解决方案是使用 DATETIMEOFFSET 格式，将值设置为统一的 1900-01-01 格式，以便记录与日期无关，并且只需要相应地调整当前系统时间。

数据

ZIP    CITY         ST  START_OFFSET            STOP_OFFSET             START_OFFSET_DST        STOP_OFFSET_DST
----------------------------------------------------------------------------------------------------------------------
10001  New York     NY  1900-01-01 09:00 -5:00  1900-01-01 18:00 -5:00  1900-01-01 09:00 -4:00  1900-01-01 18:00 -4:00
60601  Chicago      IL  1900-01-01 09:00 -6:00  1900-01-01 18:00 -6:00  1900-01-01 09:00 -5:00  1900-01-01 18:00 -5:00
80202  Denver       CO  1900-01-01 09:00 -7:00  1900-01-01 18:00 -7:00  1900-01-01 09:00 -6:00  1900-01-01 18:00 -6:00
85001  Phoenix      AZ  1900-01-01 09:00 -7:00  1900-01-01 18:00 -7:00  1900-01-01 09:00 -7:00  1900-01-01 18:00 -7:00
90001  Los Angeles  CA  1900-01-01 09:00 -8:00  1900-01-01 18:00 -8:00  1900-01-01 09:00 -7:00  1900-01-01 18:00 -7:00

询问

-- Get the current UTC date/time and adjust it to be 1900-01-01 with the current time and offset
DECLARE @UTC_TIME AS DATETIMEOFFSET = DATEADD(DAY, (DATEDIFF(DAY, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00'), '1900-01-01')) * 1, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00'))
SELECT  @UTC_TIME -- EXAMPLE: 1900-01-01 17:00:00.000000 +00:00

SELECT  TimeZoneId
FROM    dbo.TimeZones
WHERE   @UTC_TIME BETWEEN START_OFFSET AND STOP_OFFSET -- Ignoring DST logic for this example

这种方法的问题是，一旦 UTC 超过 23:59，示例中的日期/时间将是 1900-01-01 00:00:00 而不是 1900-01-02 00:00:00 并且不会下降比较时的预期范围值之间。我希望有人能帮助我在计算更好的偏移量时为树木发现森林，或者为仍然允许查询避免计算的数据结构提供一种新方法。

这个逻辑需要在 SQL Server 2008 上运行，我不想实现 CLR 来解决它。

编辑：此数据查询的目的是确定美国地区 B2B 营销的合理调用时间。我们主要使用具有偏移时区的邮政编码，如果我们没有潜在客户的邮政编码数据，我们会退回到州。我们的数据 99% 来自美国，在关岛、波多黎各等地有一些记录。如果记录给出误报，这不是世界末日，但我们确实尝试将我们的营销电话限制在当地时间 9A 到 6P对于这些前景。

如果我之前不清楚，我正在寻找一种仅跟踪时间范围的方法，而不是这些记录的日期范围。

标签： sql-serversql-server-2008-r2utcdatetimeoffset

解决方案

推荐阅读