Daylight Savings Time - 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 2000 Forums

Transact-SQL (2000)

Daylight Savings Time

Author

Topic

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-29 : 10:05:52

Does anyone have a FAST routine to determine if the current time is the hour of daylight savings time time change? (October or April) I just put this hack in a few procedures:

if getdate() between '10-31-04 1:00' and '10-31-04 2:00' return

because I know it will work and this is for production on Sunday. Something better that has been tested? These procedures are run about 30,000 times a day so speed is important since 99.9% of the time the code will have no effect.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 12:03:44

Its gonna be pretty fast, isn't it? No disk access or anything. Changing it to access a database table/column, or some other SProc or function that "knows" or whatever, sounds like more CPU effort to me.

EDIT: Scheduled task that changes the code at the Start time, and back again at the End time??

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-29 : 12:05:27

What I wrote above works fine for this Sunday but I have this problem twice a year and I need it in 3-5 places. I wanted something generic that could calculate DST days (third Sunday in October/April) accounting for leap years etc.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 12:40:34

Dunno, really. Would a lookup table, or a formula, and an hourly scheduled task that re-writes the code in 3-5 places, or sets a "global flag" - and then clears it an hour later - do? or must it happen to-the-second that daylight changing time occurs?

How do you know in the Spring anyway? i.e. time occurs twice, once for winter time, and then again for Summer time - so an "is time between" won't work, will it?

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-29 : 12:51:20

Maybe it should be done differently all together. Here is the setup; we have real-time data collection systems that record flow from gas pipelines. The information recorded is time and total volume. The problem is at 2:00 Sunday morning when the clocks fall back 1 hour, we receive readings for the 1 - 2 oclock hours twice.

1:00 23,000
1:15 24,000 -- 1000 bbls flowed this 15 minute period
1:30 25,000 -- '' ...
1:45 26,000
2:00 27,000
-- clock changes
1:00 28,000
1:15 29,000
1:30 30,000
1:45 31,000
2:00 32,000

That is stored in the database in time order as;
1:00 23,000
1:00 28,000 -- 5,000 bbls flowed
1:15 24,000 -- negative 4,000 bbls flowed? This is an error. Causes me a lot of grief
1:15 29,000 -- Undefined? Either 5,000 bbls or 1,000 bbls depending on how the last error is handled...
1:30 25,000 -- Problems continue...
1:30 30,000
1:45 26,000
1:45 31,000
2:00 27,000
2:00 32,000

My thought is to just ignore any readings on DST clock change days/hours and see a full hour of flow after the 2:00 AM hour passes.

I could optimize the code somewhat with

If Month(getdate()) in (5,10) and hour(getdate()) = 2
begin
more complex query to decide if this is the third sunday
end

that would eliminate the complex query for most days/hours

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-29 : 12:52:22

Besides, the rules are different all over the world...even in the US, some states never change...

Brett

8-)

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-10-29 : 13:10:21

Does this work?? Not sure how fast it will be???

CREATE FUNCTION dbo.DAYLIGHTENDS
(@CurrentDate datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @DayLightStart datetime

IF DATEPART(DW,CONVERT(DATETIME,'10/25/'+ CONVERT(VARCHAR,YEAR(@CurrentDate)))) = 1
SET @DayLightStart = CONVERT(DATETIME,'10/25/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')
ELSE IF DATEPART(DW,CONVERT(DATETIME,'10/26/'+ CONVERT(VARCHAR,YEAR(@CurrentDate)))) = 1
SET @DayLightStart = CONVERT(DATETIME,'10/26/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')
ELSE IF DATEPART(DW,CONVERT(DATETIME,'10/27/'+ CONVERT(VARCHAR,YEAR(@CurrentDate)))) = 1
SET @DayLightStart = CONVERT(DATETIME,'10/27/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')
ELSE IF DATEPART(DW,CONVERT(DATETIME,'10/28/'+ CONVERT(VARCHAR,YEAR(@CurrentDate)))) = 1
SET @DayLightStart = CONVERT(DATETIME,'10/28/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')
ELSE IF DATEPART(DW,CONVERT(DATETIME,'10/29/'+ CONVERT(VARCHAR,YEAR(@CurrentDate)))) = 1
SET @DayLightStart = CONVERT(DATETIME,'10/29/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')
ELSE IF DATEPART(DW,CONVERT(DATETIME,'10/30/'+ CONVERT(VARCHAR,YEAR(@CurrentDate)))) = 1
SET @DayLightStart = CONVERT(DATETIME,'10/30/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')
ELSE SET @DayLightStart = CONVERT(DATETIME,'10/31/'+ CONVERT(VARCHAR,YEAR(@CurrentDate))+ ' 01:00:00')

RETURN @DayLightStart

END

select dbo.DAYLIGHTENDS(getdate()), dateadd(hh, 1, dbo.DAYLIGHTENDS(getdate()))

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 13:26:18

"Maybe it should be done differently all together."

Run the server in GMT?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources