Determine Time Zone Offset in T-SQL

Question

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.

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'.

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?

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?

Answer 1

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:

(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.

I ... think ... :-)

Ron

Answer 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.

Maybe you could 'borrow' the data from Oracle's time zone file and build your own SQL Server function?

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':

FROM_TZ(YOUR_TIMESTAMP, 'UTC') AT TIME ZONE 'America/Dawson_Creek'

Answer 3

This seems to work, if I understand your question correctly:

select getutcdate() - getdate()

Ron

select getutcdate() - getdate() is currently returning 5 which is the wrong answer. It needs to return 6. The answer is wrong because getdate() includes the DST bias since it is currently DST in Chicago. If I were to run the same code outside of DST (say in Feb or Dec), it would return 6.

Answer 4

Check out this previous question and answer for related information:

http://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005#25073

(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.)