Daylight Savings Time correction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Daylight Savings Time correction

Author

Topic

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-08-26 : 10:28:08

Hello,

I’m wondering what is solution with automatic correction for Daylight Savings Time?

I’d like to convert from UTC (column) to local_time but with automatic correction when the time changes.

Now I use just:

Computed column:

local_time AS DATEADD(hh, -6, UTC)

or

local_time AS DATEADD(hh, -7, UTC) in winter

What is the best approach in this case?

Thx,

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-26 : 12:49:03

I wrote a UDF that can convert between two given timezones. It requires a lookup table to exist with timezone information.


----------------------------------------------------------------------------------------------------
-- OBJECT NAME		: udf_Timezone_Conversion
--
-- AUTHOR		: Tara Duggan
-- DATE			: June 5, 2002
--
-- INPUTS		: @Source_Timezone, @Destination_Timezone, 
--			  @Source_datetime, @Display_Timezone
-- OUTPUTS		: @converted_datetime
--
-- DEPENDENCIES		: None
--
-- APPLICATION(s)	: All applications can utilize this function.
--
-- DESCRIPTION		: This function converts a given date and time 
--			  from one time zone to another.
--                          
-- EXAMPLES (optional)	: SELECT dbo.udf_Timezone_Conversion('PST', 'EST', getdate(), 0)
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE FUNCTION udf_Timezone_Conversion
(@Source_Timezone varchar(25), @Destination_Timezone varchar(25), 
@Source_datetime datetime, @Display_Timezone bit = 0)
RETURNS varchar(50)
AS

BEGIN
-------------------------------------------------------------------------------------------------------------
--  Declarations
-------------------------------------------------------------------------------------------------------------
DECLARE @Source_DST bit
DECLARE @Destination_DST bit
DECLARE @converted_date datetime
DECLARE @converted_timezone varchar(50)
DECLARE @year int
DECLARE @AprilDate datetime
DECLARE @OctDate datetime
DECLARE @DST_Start datetime
DECLARE @DST_End datetime
DECLARE @GMT_Offset_Source int
DECLARE @GMT_Offset_Destination int
DECLARE @converted_datetime varchar(50)
-------------------------------------------------------------------------------------------------------------
--  Initializations
-------------------------------------------------------------------------------------------------------------
SELECT @year = DATEPART(year, @Source_datetime)
SELECT @AprilDate = 'Apr 15 ' + CONVERT(char(4), @year) 
SELECT @OctDate = 'Oct 15 ' + CONVERT(char(4), @year) 
SELECT @DST_Start = DATEADD(hour, 2, (dbo.udf_FirstSundayOfTheMonth(@AprilDate)))
SELECT @DST_End = DATEADD(hour, 2, (dbo.udf_LastSundayOfTheMonth(@OctDate)))
SELECT @DST_End = DATEADD(second, -1, @DST_End)

SELECT @GMT_Offset_Source = GMT_Offset
FROM TIMEZONE
WHERE Timezone_Name = @Source_Timezone

SELECT @GMT_Offset_Destination = GMT_Offset
FROM TIMEZONE
WHERE Timezone_Name = @Destination_Timezone

SELECT @Source_DST = DST_bit
FROM TIMEZONE
WHERE Timezone_Name = @Source_Timezone

SELECT @Destination_DST = DST_bit
FROM TIMEZONE
WHERE Timezone_Name = @Destination_Timezone
-------------------------------------------------------------------------------------------------------------
--  Check for valid inputs
-------------------------------------------------------------------------------------------------------------
IF @Source_Timezone NOT IN (SELECT Timezone_Name FROM TIMEZONE) 
  OR @Destination_Timezone NOT IN (SELECT Timezone_Name FROM TIMEZONE)
	RETURN 'You have entered an invalid time zone.'
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and both time zones observe DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1) AND (@Source_DST = 1)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source - 60, @Source_datetime)
			
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination + 60, @converted_date)
		
	IF @converted_date NOT BETWEEN @DST_Start AND @DST_End
	BEGIN
		SELECT @converted_timezone = @Destination_Timezone
		SELECT @converted_date = DATEADD(MINUTE, -60, @converted_date)
	END
	ELSE
		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
END	
-------------------------------------------------------------------------------------------------------------
--  Source data and time are not during DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination data and time are in DST after the conversion
--  If destination date and time are in DST, check if it observes DST
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime NOT BETWEEN @DST_Start AND @DST_End) 
  OR ((@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 0) AND (@Source_DST = 0))
BEGIN 
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source, @Source_datetime)
	
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination, @converted_date)

	IF (@converted_date BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1)
	BEGIN
		SELECT @converted_date = DATEADD(MINUTE, 60, @converted_date)

		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
	END
	ELSE
		SELECT @converted_timezone = @Destination_Timezone
END	
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and only source time zone observes DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 0) AND (@Source_DST = 1)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source - 60, @Source_datetime)
	
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination, @converted_date)
		
	SELECT @converted_timezone = @Destination_Timezone
END
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and only destination time zone observes DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1) AND (@Source_DST = 0)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source, @Source_datetime)
		
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination + 60, @converted_date)
		
	IF @converted_date NOT BETWEEN @DST_Start AND @DST_End
	BEGIN
		SELECT @converted_timezone = @Destination_Timezone
		SELECT @converted_date = DATEADD(MINUTE, -60, @converted_date)
	END
	ELSE
		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
END
-------------------------------------------------------------------------------------------------------------
--  Format the output using style 21
-------------------------------------------------------------------------------------------------------------	
IF @Display_Timezone = 0
	SELECT @converted_datetime = CONVERT(varchar(50), @converted_date, 21)
ELSE 
	SELECT @converted_datetime = CONVERT(varchar(50), @converted_date, 21) + '; ' + UPPER(@converted_timezone)
-------------------------------------------------------------------------------------------------------------
--  Return the output
-------------------------------------------------------------------------------------------------------------
RETURN @converted_datetime

END

Here is the table definition:


CREATE TABLE [dbo].[TIMEZONE] (
	[Timezone_Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[GMT_Offset] [float] NOT NULL ,
	[DST_Abbrv] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DST_bit] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TIMEZONE] ADD 
	CONSTRAINT [PK_TIMEZONE] PRIMARY KEY  CLUSTERED 
	(
		[Timezone_Name]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

Here is the data (only the below data is supported by this function):


+0200 	120.0	      	0

+0300 	180.0	      	0

+0400 	240.0	      	0

+0500 	300.0	      	0

+0530 	330.0	      	0

+0600 	360.0	      	0

+0700 	420.0	      	0

+0800 	480.0	      	0

+0930 	570.0	      	0

+1000 	600.0	      	0

+1100 	660.0	      	0

+1200 	720.0	      	0

-0300 	-180.0	      	0

-0400 	-240.0	      	0

-0500 	-300.0	      	0

-1100 	-660.0	      	0

-1200 	-720.0	      	0

AHST	-600.0	HDT	1

ALASKA	-540.0	ALASKA	1

ARIZ  	-420.0	      	0

AST   	-240.0	ADT   	1

CST   	-360.0	CDT   	1

EST   	-300.0	EDT   	1

GMT	0.0	 	0

HKT   	480.0	      	0

HST	-600.0	      	0

INDANA	-300.0	      	0

IRAN  	210.0	      	0

JST   	540.0	      	0

KST   	270.0	      	0

MEXCTY	-360.0	MEXCDT	1

MST   	-420.0	MDT   	1

NFLD  	-210.0	NF    	1

PST   	-480.0	PDT   	1

QUEBECE	-240.0	 	0

SASKAT	-360.0	      	0

HTH,

Tara

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-08-27 : 14:38:48

It might be too overwhelming for me!

Thank you very much!

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 14:42:20

What is too overwhelming?

It is already written. You just need to create the table, create the UDF, then insert the data.

The UDF does exactly what you need it to do. It just handles lots of timezones.

You don't even have to change anything to get it to work for you.

Once it is implemented (just add two timezones to the table to test it), you can call it like this:

SELECT dbo.udf_Timezone_Conversion('GMT', 'PST', getdate(), 0)

GMT is the same as UTC.

The first parameter is the from timezone, the second is the to timezone, the third is the date and time,
and the last parameter just says whether or not to display the timezone in the output.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-27 : 16:25:08

does it require the FirstSundayOfMonth and the LastSundayOfMonth UDF's ?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 16:36:15

Yes it does. Here they are:

[code]
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : udf_LastSundayOfTheMonth
--
-- AUTHOR : Tara Duggan
-- DATE : June 5, 2002
--
-- INPUTS : @Date
-- OUTPUTS : @Day
--
-- DEPENDENCIES : None
--
-- APPLICATION(s) : This function is called by another function, udf_Timezone_Conversion.
--
-- DESCRIPTION : This function returns the date of the last Sunday of the month
-- when given any date during that month.
--
-- EXAMPLES (optional) : SELECT dbo.udf_LastSundayOfTheMonth(getdate())
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE FUNCTION udf_LastSundayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @Lastday datetime
DECLARE @number int
DECLARE @day datetime
SELECT @weekday = 0
SELECT @Lastday = (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)))
SELECT @number = DATEPART(day, @Lastday)
WHILE @weekday <> 1
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number - 1
END
RETURN @day
END

GO

----------------------------------------------------------------------------------------------------
-- OBJECT NAME : udf_FirstSundayOfTheMonth
--
-- AUTHOR : Tara Duggan
-- DATE : June 5, 2002
--
-- INPUTS : @Date
-- OUTPUTS : @Day
--
-- DEPENDENCIES : None
--
-- APPLICATION(s) : This function is called by another function, udf_Timezone_Conversion.
--
-- DESCRIPTION : This function returns the date of the first Sunday of the month
-- when given any date during that month.
--
-- EXAMPLES (optional) : SELECT dbo.udf_FirstSundayOfTheMonth(getdate())
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE FUNCTION udf_FirstSundayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
SELECT @number = 1
SELECT @weekday = 0
WHILE @weekday <> 1
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1
END
RETURN @day
END

GO

Tara

sappollo
Starting Member

1 Post

Posted - 2004-02-09 : 08:39:48

Hi all,

I have built similar function last year. Recently I have discovered that my function takes too long to retrieve a 100000 recordtable with timezone correction due to the fact that 100000 times start of summertime is generated, 100000 times end of summertime is generated and 100000 the default offset is queried.

I made the same function for Oracle, and I adjusted this function to speed up data retrieval. The clue was to load in memory 2 tables when the user first time uses the function during a session. Table 1 holds the default offset of the timezone (which is the same for all records during retrieval) and table 2 contains start of summertime and end of summertime from year 1990 till 2030. I'm no SQL server expert, so I'm looking for a similar approach. During my search quest I ended up in this thread and I hope someone could give me a direction to success, because if I read the function made by Tara correctly, she has exactly the same problems I would have (no caching og start and end of summertime and each requery the same timezone table).

Kind regards,

Sappollo

Paw Jershauge
Starting Member

2 Posts

Posted - 2007-04-02 : 17:28:47

I know its a bit late to put this on the site, but why not just make a function that converts UTC Time to local time and call that???
This code is for danish users... we have to add 1 hour in the winter and add 2 hours in the summer..

So if you need to add 0 hours in the winter and 1 hour in the summer, just change these lines:

SET @RETURNDATA = DATEADD(hh,2,@InDateTime)

SET @RETURNDATA = DATEADD(hh,1,@InDateTime)

and

SET @RETURNDATA = DATEADD(hh,1,@InDateTime)

SET @RETURNDATA = DATEADD(hh,0,@InDateTime)


CREATE FUNCTION dbo.ConvertToLocalTime(@InDateTime datetime)
-- Developed by Paw Jershauge
RETURNS DATETIME
AS
BEGIN
           DECLARE @WorkingYear varchar(4)
           DECLARE @StartDay varchar(2)
           DECLARE @EndDay varchar(2)
           DECLARE @SUMMERTIMESTART DATETIME
           DECLARE @SUMMERTIMEEND DATETIME
           DECLARE @RETURNDATA DATETIME
           SET @StartDay = CONVERT(varchar(2),(31 - (5 * Year(@InDateTime)/4 + 4) % 7))
           SET @EndDay = CONVERT(varchar(2),(31 - (5 * Year(@InDateTime)/4 + 1) % 7))
           SET @WorkingYear = CONVERT(varchar(4),Year(@InDateTime))
           SET @SUMMERTIMESTART = CONVERT(DATETIME,@WorkingYear + '-03-' + @StartDay + ' 01:00:00',20)
           SET @SUMMERTIMEEND = CONVERT(DATETIME,@WorkingYear + '-10-' + @EndDay + ' 00:00:00',20)
           IF DateDiff(hh,@SUMMERTIMESTART,@InDateTime) >= 0 AND DateDiff(hh,@SUMMERTIMEEND,@InDateTime) <= 0
           SET @RETURNDATA = DATEADD(hh,2,@InDateTime)
           ELSE
           SET @RETURNDATA = DATEADD(hh,1,@InDateTime)
           RETURN @RETURNDATA    -- Retun Value
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-04 : 13:10:36

We can't use that as we have to do a lot of timezone conversions. We have to read the user's timezone from a table to determine what to do in the conversion. We don't just convert from one to the next. Our source is almost always GMT though. Our destination can be one of about 10 timezones or so.

Plus our way doesn't require any code changes when DST changes.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources