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 2005 Forums
 Transact-SQL (2005)
 UTC/GMT -> BST Date Conversions

Author  Topic 

mynci
Starting Member

5 Posts
Posted - 2009-08-26 : 06:58:21
Hi,

Apologies if I have missed previous threads that explain this, I am finding it hugely difficult to find a suitable solution.

I have a large dataset (45M records +), of values with a sampledate from a number of locations, samples are roughly taken every 15 minutes. There are various other tables linked in but the bulk of the queries are performing aggregation over certain time/date periods. I hope that is enough information on the data, if not please let me know.

All sampledates are datetimes and are stored in UTC/GMT, however the data must be displayed correcting for British Summer Time (essentially adding an hour between the end of March and October).

I have been reading up on this and have tried to implement a function that I 'wrap around' every reference to the sampledate which adds one to any sampledate that falls between the start and end dates of BST, however this increased the query times by a factor of around 100, I suspect due to the additional millions of calculations performed on every sampledate.

This is clearly not an acceptable solution - query times are increased to around 300-500 seconds!

The other options i am considering are adding a new field to the table that is the same as SampleDate but corrected for BST, although this is not ideal and feels a bit like failure.

Lastly i feel there may be a solution whereby I join sampledates with a table containing BST start dates, end dates and a time offset and some how add the offset to the sampledate field if the field falls between the start and end dates. is this likely to be faster than a UDF?

Are there any other options?

Regards
Stephen

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-08-26 : 07:07:13
Add a persisted calculated/computed column.

ALTER TABLE Table1 ADD MyLocalTime AS DATEADD(HOUR, -???, Col1) PERSISTED

And then include the new column in your present index(es).


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2009-08-26 : 07:21:06
As you are presumably in the same timezone as the server, and the server will keep track
of daylight saving, why not just add the difference between UTC datetime and local datetime?


-- *** Test Data ***
DECLARE @t TABLE
(
	GMTDateTime datetime NOT NULL
)
INSERT INTO @t
SELECT '20090826 07:59' UNION ALL
SELECT '20090826 08:32' UNION ALL
SELECT '20090826 08:57' UNION ALL
SELECT '20090826 09:14' UNION ALL
SELECT '20090826 10:41'
-- *** End Test Data ***

SELECT DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), GMTDateTime) AS LocalTime
FROM @t
Go to Top of Page

mynci
Starting Member

5 Posts
Posted - 2009-08-26 : 09:44:46
Thanks for your replies.

Peso your solution is very interesting and may well be the method we have to use, however I was hoping not to alter the table structure, if possible.

Ifor, I may have misunderstood your suggestion. I am in the same time zone. And the difference can be used in this way. however when averageing a few million records would this not be a similar performance hit to a user defined function? naturally i will give it a try!

i will let you know how i get on.

Regards
Stephen
Go to Top of Page

mynci
Starting Member

5 Posts
Posted - 2009-08-26 : 10:18:00
Ifor, if i have read that correctly it will let me determine the difference between utc and the current time zone ie bst, and then add this to the date extracted from the database. IT will not determine the offset required for historical data. for instanc eif i ask for daily or hourly averages for all of 2006, it will not shift the times for the BST period. or have i misunderstood?

Thanks again.
Stephen
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2009-08-26 : 10:39:20
quote:
IT will not determine the offset required for historical data


True.

Maybe try something like


-- *** Test Data ***
DECLARE @DayLight TABLE
(
	StartTime datetime NOT NULL
	,EndTime datetime NOT NULL
	,OffSet int NOT NULL
)
INSERT INTO @DayLight
SELECT '20081028 03:00', '20090329 02:00', 0 UNION ALL
SELECT '20090329 02:00', '20091025 02:00', 1 UNION ALL
SELECT '20091025 02:00', '20100328 02:00', 0

DECLARE @t TABLE
(
	GMTDateTime datetime NOT NULL
)
INSERT INTO @t
SELECT '20090226 07:59' UNION ALL
SELECT '20090326 08:32' UNION ALL
SELECT '20090826 08:57' UNION ALL
SELECT '20090926 09:14' UNION ALL
SELECT '20091226 10:41'
-- *** End Test Data ***

SELECT DATEADD(hour, L.OffSet, T.GMTDateTime) AS LocalTime
FROM @t T
	JOIN @DayLight L
		ON T.GMTDateTime >= L.StartTime
			AND T.GMTDateTime < L.EndTime
Go to Top of Page

mynci
Starting Member

5 Posts
Posted - 2009-08-27 : 04:19:12
Thanks for your help here, i think i will have to add an extra column to the table - implementing the join and dateadd has increased the execution time from about 6 seconds to 24 minutes, and it's still going!

This seems like such a basic feature that i feel a little beaten by it, any other ideas would be very welcome. if it would help i could post a version of the query in case i have dome something very silly - i am sure there are efficiencies to be had in re-writing it, but it worked satisfactorarily without the BST offsetting.

regards
Stephen
Go to Top of Page

mynci
Starting Member

5 Posts
Posted - 2009-08-27 : 04:26:45
As Discussed:

fn_getBSTRange returs a table as discussed above with values for start and end dates for BST with a 1 when it is bst and a 0 when it is not.

regards
Stephen


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('usp_GetAvgDepotVols_LR') IS NOT NULL
	DROP PROC usp_GetAvgDepotVols_LR
GO

CREATE PROCEDURE usp_GetAvgDepotVols_LR
	@StartDate datetime = NULL,
	@EndDate datetime = NULL,
	@Line varchar(30) = NULL,
	@Level varchar(50) = NULL,
	@ShiftStart smallint = 16,
	@ShiftEnd smallint = 18,
	@WeekDayStart smallint = 1,
	@WeekDayEnd smallint = 7

AS

--Set Monday as First Day of week
SET DATEFIRST 1

DECLARE @BSTTable TABLE
(
	StartDate smalldatetime,
	EndDate smalldatetime,
	Offset smallint
)

INSERT INTO @BSTTable
SELECT * FROM dbo.fn_GetBSTRange() 

IF @ShiftStart IS NULL SET @ShiftStart = 0
IF @ShiftEnd IS NULL SET @ShiftEnd = 25
IF @StartDate IS NULL SET @StartDate = '1900/01/01 00:00:00'
IF @EndDate IS NULL SET @EndDate = CONVERT(datetime,CONVERT(varchar,GETUTCDATE(),103),103)

DECLARE @DateFormat AS INTEGER

Set @DateFormat = 111

SELECT 	convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate))  AS TheDateTime,
		tbl_SystemDepots.DepotName,
		tbl_SystemDepots.DepotName + convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)),
		ROUND(AVG(tbl_VolumeData.Volume),2) as AvgVol,
		ROUND(AVG(tbl_VolumeData.PowerUsed),2) as AvgPowerUsed,
		RANK() OVER (
			Partition BY 
				convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)) 
			ORDER By 
				convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)),
				ROUND(AVG(tbl_VolumeData.Volume),2) DESC
			) As DepotRank,
			 convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)) + Convert(VARCHAR(2),RANK() OVER (Partition BY convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)) ORDER By convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)),ROUND(AVG(tbl_VolumeData.Volume),2) DESC)) As DateAndRank,
			DepotSortOrder
	FROM	
		tbl_SystemRoadMapping RIGHT OUTER JOIN
		tbl_SystemLoggers ON tbl_SystemRoadMapping.LoggerID = tbl_SystemLoggers.LoggerID INNER JOIN
		tbl_VolumeData ON tbl_SystemLoggers.LoggerID = tbl_VolumeData.LoggerID LEFT OUTER JOIN
		tbl_SystemRoads ON tbl_SystemRoadMapping.LineID = tbl_SystemRoads.LineID inner JOIN
		tbl_SystemDepots ON tbl_SystemLoggers.DepotID = tbl_SystemDepots.DepotID INNER JOIN
		tbl_SystemLoggerLevels ON tbl_SystemLoggers.LevelID = tbl_SystemLoggerLevels.LevelID Inner Join
		tbl_SystemDepotMapping ON tbl_SystemRoadMapping.LineID = tbl_SystemDepotMapping.LineID 
		AND tbl_SystemLoggers.DepotID = tbl_SystemDepotMapping.DepotID FULL OUTER JOIN
		@BSTTable as BSTData ON tbl_VolumeData.SampleDate  >=  BSTData.StartDate AND 
				 tbl_VolumeData.SampleDate <= BSTData.EndDate
	WHERE	
			(tbl_SystemRoads.RoadName LIKE @Line 
				OR @Line IS NULL)
			AND (tbl_SystemLoggerLevels.LevelName = @Level
				OR @Level IS NULL)

			AND DATEPART(hh,DATEADD(hour, BSTData.OFFSET, tbl_VolumeData.SampleDate)) BETWEEN @ShiftStart AND @ShiftEnd
			AND DATEPART(dw,DATEADD(hour, BSTData.OFFSET, tbl_VolumeData.SampleDate)) BETWEEN @WeekDayStart AND @WeekDayEnd
			AND DATEADD(hour, BSTData.OFFSET, tbl_VolumeData.SampleDate) BETWEEN @StartDate AND @EndDate
	GROUP BY 
		convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)),
		tbl_SystemDepots.DepotName,
		tbl_SystemDepots.DepotName + convert(varchar(2),Datepart(mm,DATEADD(hour, BSTData.OFFSET, SampleDate))) + '/' + convert(varchar(4),Datepart(yyyy,SampleDate)),
		tbl_SystemDepotMapping.DepotSortOrder
ORDER BY TheDateTime ASC,DepotSortOrder Asc
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site