确定 T-SQL 中的时区偏移
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/180638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Determine Time Zone Offset in T-SQL
提问by Andy Frieders
My database application is going to be deployed at multiple sites in different time zones.
我的数据库应用程序将部署在不同时区的多个站点。
I need a T-SQL function that will determine the UTC timestamp of midnight on January 1 of the current year for YTD calculations. All of the data is stored in UTC timestamps.
我需要一个 T-SQL 函数来确定当年 1 月 1 日午夜的 UTC 时间戳以进行 YTD 计算。所有数据都存储在 UTC 时间戳中。
For example, Chicago is UTC-6 with Daylight Savings Time (DST), the function needs to return '2008-01-01 06:00:00' if run any time in 2008 in Chicago. If run in New York (GMT-5 + DST) next year, it needs to return '2009-01-01 05:00:00'.
例如，芝加哥是 UTC-6 夏令时 (DST)，如果在芝加哥 2008 年的任何时间运行，该函数需要返回 '2008-01-01 06:00:00'。如果明年在纽约 (GMT-5 + DST) 运行，则需要返回 '2009-01-01 05:00:00'。
I can get the current year from YEAR(GETDATE()). I thought I could do a DATEDIFFbetween GETDATE()and GETUTCDATE()to determine the offset but the result depends on whether the query is run during DST or not. I do not know of any built in T-SQL functions for determining the offset or whether or not the current time is DST or not?
我可以从YEAR(GETDATE()). 我想我可以做一个DATEDIFF之间GETDATE()并GETUTCDATE()确定偏移，但结果取决于查询是否DST期间或无法运行。我不知道有任何内置的 T-SQL 函数来确定偏移量或当前时间是否为夏令时？
Does anyone have a solution to this problem in T-SQL? I could hard code it or store it in a table but would prefer not to. I suppose that this is a perfect situation for using CLR Integration in SQL Server 2005. I am just wondering if there is a T-SQL solution that I am unaware of?
有没有人在 T-SQL 中解决这个问题？我可以对其进行硬编码或将其存储在表中，但我不想这样做。我想这是在 SQL Server 2005 中使用 CLR 集成的完美情况。我只是想知道是否有我不知道的 T-SQL 解决方案？
采纳答案by Eric Z Beard
Check out this previous question and answer for related information:
查看之前的问题和答案以获取相关信息：
Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005
在 SQL 2005 中有效地在 UTC 和本地（即 PST）时间之间转换日期
(To summarize, you do need to build time zone and DST tables in Sql Server 2005. In the next version of Sql Server we get some help with time zones.)
（总而言之，您确实需要在 Sql Server 2005 中构建时区和 DST 表。在下一个版本的 Sql Server 中，我们将获得有关时区的一些帮助。）
回答by Ron Savage
Hmm, I guess I'm not understanding the problem. If the database app is already storing UTC timestamps for all of it's transactions, and you want to sum up some values since the first of the year "local time", your condition would have to be something like:
嗯，我想我没有理解这个问题。如果数据库应用程序已经为它的所有事务存储了 UTC 时间戳，并且您想总结自“本地时间”第一年以来的一些值，则您的条件必须是这样的：
(timestamp + (getutcdate() - getdate())) > cast('01/01/2008' as datetime)
The DST can be onor offdepending on when in the year the query is run - but getdate()takes it into account, so you have to dynamically calculate the offset every time.
DST 可以打开或关闭，具体取决于查询在一年中的何时运行 - 但getdate()将其考虑在内，因此您必须每次动态计算偏移量。
回答by neonski
Unless I'm mistaken, the GETUTCDATE() function uses the time zone defined on the server - it has no information regarding the client's time zone (or any time zone). I don't think that information is stored anywhere in SQL Server 2005, which makes it impossible for it to calculate this information.
除非我弄错了，否则 GETUTCDATE() 函数使用服务器上定义的时区 - 它没有关于客户端时区（或任何时区）的信息。我不认为该信息存储在 SQL Server 2005 中的任何位置，这使得它无法计算此信息。
Maybe you could 'borrow' the data from Oracle's time zone fileand build your own SQL Server function?
也许您可以从Oracle 的时区文件中“借用”数据并构建您自己的 SQL Server 函数？
Off topic (could be useful to someone else) but if you were using Oracle, you could use the FROM_TZ function and 'AT TIME ZONE':
题外话（可能对其他人有用）但如果您使用的是 Oracle，则可以使用 FROM_TZ 函数和“AT TIME ZONE”：
FROM_TZ(YOUR_TIMESTAMP, 'UTC') AT TIME ZONE 'America/Dawson_Creek'
