Create a UTC/GMT lookup - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Create a UTC/GMT lookup

Author

Topic

welshman10
Starting Member

4 Posts

Posted - 2011-05-05 : 06:46:40

I use a database that stores all it's datetimestamps in the UTC format. I currently use a function to convert these into GMT, to account for British Summertime.

It works fine in terms of altering the datetime that is to be used for reporting, but the problem is that it slows queries down very significantly.

I think it might be better to come up with some sort of lookup table that I can then join, in order to have a datetime field I can use for reporting. Does anyone have a script for this, I'm sure other people must have this same issue?

Function shown below for reference
ALTER FUNCTION [dbo].[bstdate] (@utcdate AS DATETIME)
RETURNS DATETIME
AS
BEGIN
Declare @d table(
bstYear INT,
bstStart DATETIME,
bstEnd DATETIME,
PRIMARY KEY (bstYear)
)
INSERT @d VALUES (2004,'28 Mar 2004 01:00:00','31 Oct 2004 02:00:00')
INSERT @d VALUES (2005,'27 Mar 2005 01:00:00','30 Oct 2005 02:00:00')
INSERT @d VALUES (2006,'26 Mar 2006 01:00:00','29 Oct 2006 02:00:00')
INSERT @d VALUES (2007,'25 Mar 2007 01:00:00','28 Oct 2007 02:00:00')
INSERT @d VALUES (2008,'30 Mar 2008 01:00:00','26 Oct 2008 02:00:00')
INSERT @d VALUES (2009,'29 Mar 2009 01:00:00','25 Oct 2009 02:00:00')
INSERT @d VALUES (2010,'28 Mar 2010 01:00:00','31 Oct 2010 02:00:00')
INSERT @d VALUES (2011,'27 Mar 2011 01:00:00','30 Oct 2011 02:00:00')
INSERT @d VALUES (2012,'25 Mar 2012 01:00:00','28 Oct 2012 02:00:00')
INSERT @d VALUES (2013,'31 Mar 2013 01:00:00','27 Oct 2013 02:00:00')
INSERT @d VALUES (2014,'30 Mar 2014 01:00:00','26 Oct 2014 02:00:00')
INSERT @d VALUES (2015,'29 Mar 2015 01:00:00','25 Oct 2015 02:00:00')
INSERT @d VALUES (2016,'27 Mar 2016 01:00:00','30 Oct 2016 02:00:00')

DECLARE @result DATETIME
SELECT @result =
CASE
WHEN @utcdate BETWEEN bstStart AND bstEnd THEN DATEADD(hh,1,@utcdate)
ELSE @utcdate
END
FROM @d WHERE bstYear = YEAR(@utcdate)
IF @result IS NULL SET @result = @utcdate
RETURN @result
END

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2011-05-05 : 06:54:34

You might find Michaels script useful http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

-----------

If debugging is the process of removing software bugs, then programming must be the process of putting them in.

welshman10
Starting Member

4 Posts

Posted - 2011-05-05 : 07:43:37

It does look useful, but not for this particular issue (unless I'm missing the point!), as it doesn't utilise times, only dates.

What I need is to have 2 columns, so that I can join on one, but report against the other eg
UTC-------------------BST
05/05/2011 14:39:25---05/05/2011 15:39:25
...
05/05/2011 23:59:59---06/05/2011 00:59:59

I guess we can leave the minutes and seconds off/set them all to 00:00 and add them back in later, as they will remain constant and unaffected by the +1 hour added

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-05-05 : 10:53:36

If you need to convert from UTC time to local time, you would be better of creating a permanent table with the start datetime, end datetime, and UTC offset for each range and just join to that table. If you need to work with multiple time zones, you should add a time zone ID to the table. A join to a table will be much faster than calling a function for each value.


create table #UTCOffSet
(
UTCStartDatetime	datetime	not null,
UTCEndDatetime		datetime	not null,
UTCOffSet		datetime	not null
primary key clustered ( UTCStartDatetime, UTCEndDatetime)
)

insert into  #UTCOffSet
select	'20100328 01:00', '20101031 02:00', '01:00:00'	union all
select	'20101031 02:00', '20110327 01:00', '00:00:00'	union all
select	'20110327 01:00', '20111030 02:00', '01:00:00'	union all
select	'20111030 02:00', '20120325 01:00', '0:00:00'	union all
select	'20120325 01:00', '20121028 02:00', '01:00:00'


select
	a.MyDate+b.UTCOffSet
from
	MyTable a
	join
	#UTCOffSet b
	on	a.MyDate >= a.UTCStartDatetime and
		a.MyDate <  b.UTCEndDatetime

CODO ERGO SUM

welshman10
Starting Member

4 Posts

Posted - 2011-05-05 : 11:44:52

That's a great idea, didn't think of doing it that way

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-05-05 : 12:02:57

Just make sure that you don't leave any gaps or overlaps in the ranges, and that you don't forget to update the table every year to add new years.

And make sure you join in this form:

a.MyDate >= a.UTCStartDatetime and a.MyDate <  b.UTCEndDatetime

not

a.MyDate between a.UTCStartDatetime and b.UTCEndDatetime

The last form could return two rows if the time matches the start or end datetime exactly.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources