Longing to know

If you’ve read my various posts on dealing with datetimes and timezones lately, I discovered a handy little mechanism for dealing with timezones in Microsoft SQL.

Windows stores all Timezone information in the Registry. Fortunately, Microsoft SQL, through T-SQL, can access and query the registry. Below is a simple query that will pull all of the timezone information from the registry and put it all into a temporary table. The timezone offset is determined by performing a split/parse of the offset string from the registry. This information can be used to convert between zones.

SET NOCOUNT ON;

DECLARE @root varchar(1000) = 'HKEY_LOCAL_MACHINE';
DECLARE @key varchar(1000) = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones';
DECLARE @tzName varchar(1000);
DECLARE @tzKey varchar(1000);
DECLARE @offset varchar(1000);
DECLARE @tzi binary(56);
DECLARE @substr varchar(1000);
DECLARE @pos int;
DECLARE @hours int;
DECLARE @minutes int;
DECLARE @start int;
DECLARE @end int;
DECLARE @isNegative bit;
DECLARE @utcOffset INT = DATEDIFF(minute, GETDATE(), GETUTCDATE());
DECLARE @parsedOffset INT;
DECLARE @midnight datetime2 = CAST(DATEFROMPARTS(DATEPART(year, GETDATE()), DATEPART(month, GETDATE()), DATEPART(day, GETDATE())) AS datetime2);
DECLARE @testdate datetime2 = CAST(DATEFROMPARTS(DATEPART(year, GETDATE()), DATEPART(month, GETDATE()), DATEPART(day, GETDATE())) AS datetime2);

DECLARE @timeZoneNames TABLE
(
  SubKeyName varchar(1000)
)

DECLARE @timeZones TABLE
(
  Name varchar(1000),
  Offset varchar(1000),
  ParsedOffset int,
  BiasMinutes int,
  ExtraBias_Std int,
  ExtraBias_DST int
)

INSERT INTO @timeZoneNames EXEC master..xp_regenumkeys @root, @key

DECLARE tz_cursor CURSOR FOR  
SELECT SubKeyName FROM @timeZoneNames 

OPEN tz_cursor   
FETCH NEXT FROM tz_cursor INTO @tzName   

WHILE @@FETCH_STATUS = 0   
BEGIN
       SET @tzKey = @key + '\' + @tzName
       exec master.dbo.xp_regread @root, @tzKey, 'Display', @offset OUTPUT
       SET @pos  = CHARINDEX(':', @offset);

       IF @pos > 0
       BEGIN
              SET @substr = SUBSTRING(@offset, 1, @pos);
              SET @isNegative = CASE WHEN PATINDEX('%-%', @substr) > 0 THEN 1 ELSE 0 END;
              SET @start = CASE WHEN @isNegative = 1 THEN PATINDEX('%-%', @substr) ELSE PATINDEX('%+%', @substr) END + 1;
              SET @end = PATINDEX('%[^0-9]%', SUBSTRING(@substr, @start, LEN(@substr)))-1
              SET @hours = CAST(SUBSTRING(@substr, @start, @end) AS int) * CASE WHEN @isNegative = 1 THEN -1 ELSE 1 END;

              SET @substr = SUBSTRING(@offset, @pos, LEN(@offset));
              SET @start = PATINDEX('%[0-9]%', @substr);
              SET @end = PATINDEX('%[^0-9]%',SUBSTRING(@substr, @start, LEN(@substr)))-1
              SET @minutes = SUBSTRING(@substr, @start, @end);

              exec master.dbo.xp_regread @root, @tzKey, 'TZI', @tzi OUTPUT
              INSERT INTO @timeZones
              SELECT
              @tzName, 
              @offset,
              @hours * 60 + @minutes,
              -- See http://msdn.microsoft.com/ms725481
              CAST(CAST(REVERSE(SUBSTRING(@tzi,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
              CAST(CAST(REVERSE(SUBSTRING(@tzi,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
              CAST(CAST(REVERSE(SUBSTRING(@tzi,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST;
       END
       FETCH NEXT FROM tz_cursor INTO @tzName  
END   

CLOSE tz_cursor   
DEALLOCATE tz_cursor

-- Let's say the dealer makes a sale at 8PM PST
SET @testdate = DATEADD(hour, 18, CAST(DATEFROMPARTS(DATEPART(year, GETDATE()), DATEPART(month, GETDATE()), DATEPART(day, GETDATE())) AS datetime2));

-- What does that look like in UTC time? What does our configured date at midnight look like?  What does midnight in Knoxville look like in UTC?
-- This shows that we have to have a point of reference to compare the configured dates.  They either need a fixed offset for the region or by sale.
SELECT TOP 1 @parsedOffset = ParsedOffset FROM @timeZones WHERE Name LIKE 'Pacific Standard Time';
SELECT DATEADD(minute, @parsedOffset, @testdate) AS ConvertPSTToUTC, DATEADD(minute, @parsedOffset, DATEADD(day, 1, @midnight)) AS MidnightUTC, DATEADD(minute, @utcOffset, @midnight) AS LocalMidnightUTC

SELECT Name, ParsedOffset, DATEADD(minute, @utcOffset, @midnight) AS LocalMidnight, DATEADD(minute, ParsedOffset, @midnight) AS TimeZoneMidnight FROM @timeZones