如何在SQL Server 2012中的SQLCLR程序集中使用TimeZoneInfo

Question

我想在SQL Server 2012中实现时区转换。但是，TimeZoneInfo标记有 MayLeakOnAbort
属性。调用我定义的SQL函数（使用TimeZoneInfo）时，这会导致运行时错误。

错误报告如下

System.Security.HostProtectionException：尝试执行CLR主机禁止的操作。

受保护的资源（仅在完全信任的情况下可用）是：全部

所需资源为：MayLeakOnAbort

文档提示我可以使用“ SafeHandle”解决此泄漏问题，但是我看不到如何解决。

那么，如何在SQLCLR上下文中使用TimeZoneInfo类呢？

Answer 1

更新的答案

我已经写了我在原始答案中提到的实用程序，您可以在此处找到。

此外，从SQL Server 2016（和Azure SQL数据库）开始，您现在可以使用AT TIME ZONE关键字在时区之间进行转换。

原始答案

不幸的是，在SQL Server中没有使用时区的出色解决方案。

我调查了大量你链接的问题，随着这一个也。没有内置的时区功能，TimeZoneInfo在SQLCLR中的任何使用都需要将该程序集注册为“不安全”。这通常是不希望的。

我还研究了使用SQLCLR中的Noda
Time
。您可以在本期中阅读有关它的信息。由于某些项目在内部缓存的方式，还必须将其注册为“不安全”。

最终，无论使用哪种方法，问题都是无法在SQLCLR中缓存任何内容。您不能以线程安全的方式使用静态变量，也不能进行任何线程同步或使用诸如之类的类ConcurrentDictionary。SQL希望完全控制程序集的线程模型。在“安全”程序集中，仅单线程使用一次扔掉的样式代码可以工作。

希望 最终 将有一个可以在SQLCLR中运行的Noda
Time构建，但是它将是一个不进行任何缓存的特殊构建。因此它的执行速度不会那么快，但可以在确保安全的同时完成工作。

TimeZoneInfo不太可能改变。因此，除非SQL Server团队曾经将时区函数直接正确地带入SQL
Server（如Oracle和Postgres那样），否则您只有几个选择：

• 不要尝试在数据层中进行时区转换。使用UTC中的datetime或datetime2值，或使用datetimeoffset带任何偏移量的值。但是，请在应用程序层的时区之间进行所有转换。这是我目前的最佳建议。

• 将时区的所有数据复制到实际的SQL表中，并编写适用于该数据的函数。这不是最好的主意，因为数据经常更改，因此表维护可能是一个挑战。要使功能（包括夏令时更改的所有规则）准确无误，也可能是具有挑战性的。我不知道有哪个项目可以很好地将其捆绑在一起，但是如果有人可以-请在评论中让我知道。

• xp_regread直接从Windows注册表项启用并使用时区数据。更新将为您完成，但是在编写这些功能时您仍然面临相同的挑战。无论如何，启用注册表读取可能与启用不安全的CLR程序集一样具有安全风险。

我正在考虑的另一个想法是编写IANA / Olson TZDB解析器和专门用于SQL
Server的功能。这与上面的选项2相似，但是以可维护的方式完成，并且使用IANA标准数据而不是Windows时区。也许有一天我会去，或者有人会击败我。同样，我目前还不知道有任何项目可以做到这一点，但是如果有人知道一个项目，请在评论中让我知道。（
完成-请参见顶部的更新 ）

关于SWITCHOFFSET-仅在您已经知道目标偏移量的情况下才有效。这是成功的一半，也可能是为什么Microsoftdatetimeoffset在文档中仍将其标记为“不了解夏令时”的原因。