如何在SQL Server中正确处理TimeZone？

#1 楼

不幸的是，没有快速解决方案。应用程序的国际化应作为最初设计讨论的一部分，因为它实际上涉及许多不同领域的核心，包括日期/时间比较和输出格式。

无论如何，继续在正确行事的轨道上，必须将时区信息与时间一起存储。换句话说，意识到日期/时间20130407 14:50是没有意义的，而没有（a）包括时区当时的UTC偏移量（注1），或者（b）确保所有插入这些值的逻辑先转换为某个固定偏移量（最有可能为0）。没有这些东西，两个给定的时间值是不可比的，并且数据已损坏。 （顺便说一句，后一种方法正在玩火（注2）；请不要这样做。）

在SQL Server 2008+中，您可以使用datetimeoffset数据类型。 （为完整起见，在2005年及之前，我将添加第二列以存储当时的UTC偏移值（以分钟为单位）。）

这使得桌面型应用程序变得容易，因为这些平台通常具有自动将日期/时间+时区转换为本地时间，然后根据用户的区域设置进行格式化以进行输出的机制。

对于网络而言，这是固有的断开连接架构，即使正确设置了后端数据，它也更加复杂，因为您需要有关客户端的信息才能进行转换和/或格式化。这通常是通过用户首选项设置（应用程序在输出之前转换/格式化事物）来完成的，或者只是对所有人显示具有相同固定格式和时区偏移的事物（这是Stack Exchange平台当前所做的事情）。

您会看到如果后端数据设置不正确，如何很快使它变得复杂和棘手。我不建议您选择任何一条路径，因为这样一来您最终会遇到更多问题。

注1：

时区的UTC偏移量是固定的：考虑夏令时，其中某个区域的UTC偏移量会相差一个小时或一个小时以下。另外，区域的夏令时日期也会定期变化。因此，使用datetimeoffset（或local time和UTC offset at that time的组合）可以最大程度地恢复信息。

注2：

这与控制数据输入有关。虽然没有万无一失的方法来验证输入值，但最好执行一个不涉及计算的简单标准。如果公共API期望包含偏移量的数据类型，则调用方将清楚此要求。

如果不是这种情况，则调用方必须依赖文档（如果他们读取了该文档） ），或者计算不正确等。在需要偏移量时，故障/错误模式较少，特别是对于分布式系统（或者甚至是单独服务器上的Web /数据库）。

反正存储偏移量会用一颗石头杀死两只鸟；即使现在不需要，也可以在以后根据需要使用。的确，它占用了更多的存储空间，但是我认为这是值得进行权衡的，因为如果一开始就从未记录过数据，则会丢失数据。

#2 楼

我为SQL Server中的时区转换开发了一个全面的解决方案。请参阅GitHub上的SQL Server时区支持。

它可以正确处理时区之间以及往返于UTC的转换，包括夏令时。

概念上与adss的答案中描述了“ T-SQL工具箱”解决方案，但它使用的是更常见的IANA / Olson / TZDB时区，而不是Microsoft的时区，并且它包含实用程序，可在TZDB新版本发布时保持数据的维护。
示例用法（回答OP）：

SELECT Tzdb.UtcToLocal(sysutcdatetime(), 'Asia/Riyadh') as CurrentTimeInSaudiArabia

有关其他API和详细说明，请参见GitHub上的自述文件。

另外，请注意，由于这是基于UDF的解决方案，因此其设计并非以性能为主要目标。如果将此功能内置到SQL Server中，还是更好，类似于Oracle和MySQL中CONVERT_TZ函数的存在方式。

#3 楼

SQL Server从2005年开始进行了修改，将内部时区保存在UTC中。这主要是由于地理复制和HA投影仪涉及到原木运输，并且原木运输时间保存在不同的时区中，使得旧方法无法恢复原木。

因此，在UTC时间内部保存所有内容使SQL Server可以在全局范围内正常工作。这就是为什么夏令时在Windows中很难解决的原因之一，因为其他MS产品（例如Outlook）也在内部将日期/时间保存为UTC，并创建了需要修补的偏移量。

我在一家公司工作，那里有成千上万的服务器（虽然不是MS SQL Server，而是各种各样的服务器）分布在世界各地，如果我们不专门强制所有工作，通过UTC，我们所有人都会很快疯掉。

#4 楼

我已经在Codeplex上开发并发布了“ T-SQL工具箱”项目，以帮助在SQL Server中处理日期时间和时区问题的任何人。它是开源的，完全免费使用。

它提供了使用普通T-SQL轻松进行日期时间转换的UDF，并带有其他现成的配置表。

在您的示例中，您可以使用以下示例：

SELECT [DateTimeUtil].[UDF_ConvertLocalToLocalByTimezoneIdentifier] (
    'GMT Standard Time', -- the original timezone in which your datetime is stored
    'Middle East Standard Time', -- the target timezone for your user
    '2014-03-30 01:55:00' -- the original datetime you want to convert
)

这将为您的用户返回转换后的日期时间值。

所有受支持的时区的列表可以是T-SQL工具箱数据库中还提供了在表“ DateTimeUtil.Timezone”中找到的信息。

#5 楼

我可能会遗漏一些明显的东西，但是如果您要做的只是使用用户时区和语言格式显示日期，最简单的方法是始终将日期存储在数据库中的UTC中，并让客户端应用程序从UTC转换为本地时区并使用用户区域设置相应地格式化日期。