从 SQL 2005 Server 访问 TimeZoneInfo

问题描述

.NET TimeZoneInfo 类很棒，我认为它可以解决我在 SQL 2005 数据库中记录来自多个时区的数据的所有问题.

The .NET TimeZoneInfo class is great and I thought it would answer all my issues with recording data from multiple time zones in my SQL 2005 database.

要将数据库中的 UTC 日期时间转换为任何其他时区，我只需使用 TimeZoneInfo.FindSystemTimeZoneById() 将时区转换为 TimeZoneInfo 类，然后调用 TimeZoneInfo.ConvertTimeFromUtc().杰出的！我只是从 SQL .NET CLR 调用它！

To convert a UTC datetime in the database to any other time zone i'd just get the time zone into a TimeZoneInfo class using TimeZoneInfo.FindSystemTimeZoneById() and then call the TimeZoneInfo.ConvertTimeFromUtc(). Brilliant! I'd just call this from the SQL .NET CLR!

但是...TimeZoneInfo 的主机保护属性为 MayLeakOnAbort.

BUT...TimeZoneInfo has a Host Protection Attribute of MayLeakOnAbort.

当我使用 VS 2008 创建 SQL 函数或存储过程时，我什至看不到 system.TimeZoneInfo 类，没关系使用它.我还假设即使我可以以某种方式引用 TimeZoneInfo 类，如果我尝试在 SQL Sever 2005 中注册程序集，我可能会遇到某种安全异常.

When I use VS 2008 to create an SQL function or stored procedure, I cannot even see the system.TimeZoneInfo class nevermind use it. I'm assuming also that even if I could somehow reference the TimeZoneInfo class, I would probably get some sort of security exception if I tried to register the assembly in SQL Sever 2005.

帮助！有什么方法可以从 SQL Server 2005 访问 TimeZoneInfo 类及其所有财富?

Help! Is there any way to access TimeZoneInfo class and all its riches from SQL Server 2005?

注意:我刚刚在第一个答案之后添加了这个警告:

NB: I've just added this caveat after the first Answer:

我们在世界各地都有站点.我们需要在数据库中存储本地时间和 UTC 时间，以应对可能需要在站点级别进行趋势分析的事件.一个趋势可能在一年内包含超过 52,000 个数据点，因此，为了提高效率，我不能只在数据库中以 UTC 格式存储时间并转换客户端上的每个数据点.因此，我需要能够在数据库中将任何时区的本地时间与 UTC 时间相互转换.

We have sites at different locations around the world. We need to store local time and UTC time in the database against events which may require trending at Site level. A trend may consist of over 52,000 data points over a year, so, for efficiency, I cannot just store times in UTC in the DB and convert every datapoint on the client. Thus I need the ability, within the DB to convert a local time in any timezone to and from UTC time.

推荐答案

我刚刚在 SQL 2008 数据库上完成了这个.

I just finished doing this on a SQL 2008 database.

首先，我必须将数据库设置为值得信赖并验证所有者是否正确.

First I had to set the DB to trustworthy and verify the owner was correct.

use [myDB]
go
alter database [myDB] set trustworthy on
go

exec sp_changedbowner 'sa'
go

接下来，我创建了一个 .NET 解决方案

Next, I created a .NET solution

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Collections.ObjectModel
Imports System.Runtime.InteropServices

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub sp_ConvertTime(ByVal UTCTime As DateTime, ByVal ZoneID As String, <Out()> ByRef Output As DateTime)
    Dim sp As SqlPipe = SqlContext.Pipe

    Dim ConvertedTime As DateTime
    Dim tzUTC = TimeZoneInfo.FindSystemTimeZoneById("UTC")
    Dim tzNew = TimeZoneInfo.FindSystemTimeZoneById(ZoneID)

    ConvertedTime = TimeZoneInfo.ConvertTime(UTCTime, tzUTC, tzNew)

    Output = ConvertedTime
    sp.Send(ConvertedTime)

    ConvertedTime = Nothing
    tzUTC = Nothing
    tzNew = Nothing
    sp = Nothing

End Sub
End Class

在部署之前，我将权限级别设置为 不安全.

Before deployment I set the Permission level to Unsafe.

接下来我将其部署出来，检查了输出窗口中的构建错误并更正了这些错误.

Next I deployed it out I checked the Output window for Build errors and corrected those.

这是 SQL 测试

DECLARE @UTCTime datetime
DECLARE @ZoneID varchar(21)
DECLARE @NewTime datetime

SET @UTCTime = GETUTCDATE()
SET @ZoneID = 'Central Standard Time'

exec sp_ConvertTime @UTCTime, @ZoneID, @NewTime OUTPUT
select @NewTime AS NewTime

这篇关于从 SQL 2005 Server 访问 TimeZoneInfo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！