How to deal with different local timezones with MSSQL
- If you want to combine multiple data files that have dates, you will need to take the timezones in consideration.
- You can't just merge the data files. If you try to merge data files with the local timezone of EST [Eastern Standard Time] with a file from Western Australia [WST], Japan, China... you will have gone through the International Date Line, meaning you will be a day off!
- You will also need to calculate the offset for the daylight saving time.
- The better way is to keep all these dates in UTC: Universal Coordinated Time see: How to keep dates in UTC with MSSQL
- Or you will have to calculate the time offset to merge together.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT counter,emp_short,trans_date,debit,credit FROM trans02;
- go
counter emp_short trans_date debit credit ----------- --------- ----------------------- --------------------- --------------------- 10 000190 2007-06-01 00:00:00.000 NULL 125.00 20 000110 2007-06-01 00:00:00.000 NULL 100.00 30 000190 2007-06-02 00:00:00.000 25.00 NULL 40 000030 2007-07-15 00:00:00.000 357.00 NULL 50 000030 2007-07-16 00:00:00.000 NULL 22.00 60 000030 2007-07-18 00:00:00.000 NULL 41.00 70 000030 2007-07-20 00:00:00.000 NULL 29.50 80 000110 2007-07-20 00:00:00.000 42.00 NULL 90 000110 2007-07-22 00:00:00.000 66.00 NULL 100 000110 2007-07-23 00:00:00.000 77.00 NULL 110 000010 2007-07-23 00:00:00.000 11.00 NULL 120 000010 2007-07-24 00:00:00.000 22.56 NULL 130 000010 2007-07-25 00:00:00.000 33.69 NULL NULL 000070 2007-09-25 00:00:00.000 NULL 11.11 NULL 000070 2007-09-26 00:00:00.000 NULL 11.22 NULL 000070 2007-09-27 00:00:00.000 55.00 NULL NULL 000070 2007-10-01 00:00:00.000 52.50 NULL NULL 000160 2007-10-20 00:00:00.000 9.00 NULL NULL 000160 2007-10-20 01:00:00.000 11.50 NULL NULL 000160 2007-10-21 00:00:00.000 NULL 45.00 (20 row(s) affected)
Converting local time to UTC
- DECLARE @offset SMALLINT
- SELECT @offset = DATEDIFF(MINUTE, GETUTCDATE(), CURRENT_TIMESTAMP)
- SELECT counter,emp_short,trans_date AS 'Local date - PST',
- DATEADD(MINUTE, @offset, trans_date) AS 'UTC date',
- @offset AS 'Offset in minutes',
- debit,credit
- FROM trans02;
- go
counter emp_short Local date - PST UTC date Offset in minutes debit credit ----------- --------- ----------------------- ----------------------- ----------------- --------------------- --------------------- 10 000190 2007-06-01 00:00:00.000 2007-05-31 16:00:00.000 -480 NULL 125.00 20 000110 2007-06-01 00:00:00.000 2007-05-31 16:00:00.000 -480 NULL 100.00 30 000190 2007-06-02 00:00:00.000 2007-06-01 16:00:00.000 -480 25.00 NULL 40 000030 2007-07-15 00:00:00.000 2007-07-14 16:00:00.000 -480 357.00 NULL 50 000030 2007-07-16 00:00:00.000 2007-07-15 16:00:00.000 -480 NULL 22.00 60 000030 2007-07-18 00:00:00.000 2007-07-17 16:00:00.000 -480 NULL 41.00 70 000030 2007-07-20 00:00:00.000 2007-07-19 16:00:00.000 -480 NULL 29.50 80 000110 2007-07-20 00:00:00.000 2007-07-19 16:00:00.000 -480 42.00 NULL 90 000110 2007-07-22 00:00:00.000 2007-07-21 16:00:00.000 -480 66.00 NULL 100 000110 2007-07-23 00:00:00.000 2007-07-22 16:00:00.000 -480 77.00 NULL 110 000010 2007-07-23 00:00:00.000 2007-07-22 16:00:00.000 -480 11.00 NULL 120 000010 2007-07-24 00:00:00.000 2007-07-23 16:00:00.000 -480 22.56 NULL 130 000010 2007-07-25 00:00:00.000 2007-07-24 16:00:00.000 -480 33.69 NULL NULL 000070 2007-09-25 00:00:00.000 2007-09-24 16:00:00.000 -480 NULL 11.11 NULL 000070 2007-09-26 00:00:00.000 2007-09-25 16:00:00.000 -480 NULL 11.22 NULL 000070 2007-09-27 00:00:00.000 2007-09-26 16:00:00.000 -480 55.00 NULL NULL 000070 2007-10-01 00:00:00.000 2007-09-30 16:00:00.000 -480 52.50 NULL NULL 000160 2007-10-20 00:00:00.000 2007-10-19 16:00:00.000 -480 9.00 NULL NULL 000160 2007-10-20 01:00:00.000 2007-10-19 17:00:00.000 -480 11.50 NULL NULL 000160 2007-10-21 00:00:00.000 2007-10-20 16:00:00.000 -480 NULL 45.00 (20 row(s) affected)
- You should calculate the different timezones in minutes and not in hours. There are a few countries/regions that use the 30mn timezone change.
- Line 1: declare @offset smallint: The highest value can only be 1440 = 24 hours, so smallint is ok.
- Line 2: select @offset = datediff(minute, getutcdate(), current_timestamp): Gets the offset from the Windows Server OS setting [date/time].
- You should use current_timestamp instead of getdate() since it will be soon deprecated by Microsoft.
- Line 4: dateadd(minute, @offset, trans_date): change the date and time to UTC.
- My offset is -480 minutes or 8 hours from the GMT/UTC. My timezone is PST: Pacific Standard Time.
- The calculation of @offset only exists between the go statement.
- Go will deallocate the variable @offset.