在T-SQL中确定时区偏移量

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函数来确定YTD计算当前年1月1日午夜的UTC时间戳。所有数据都存储在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'.

例如,芝加哥是夏令时(DST)的UTC-6,如果在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 DATEDIFF between 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())。我以为我可以在GETDATE()和GETUTCDATE()之间执行DATEDIFF来确定偏移量,但结果取决于查询是否在DST期间运行。我不知道有任何内置的T-SQL函数来确定偏移量或者当前时间是否为DST?

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解决方案?

3 个解决方案

#1

Check out this previous question and answer for related information:

在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中,我们可以获得有关时区的帮助。)

#2

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 file and 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'

#3

Hm, 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)

(timestamp +(getutcdate() - getdate()))> cast('01 / 01/2008'as datetime)

The DST can be on or off depending 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()会将其考虑在内,因此您必须每次都动态计算偏移量。

I ... think ... :-)

我认为 ... :-)

#1