Lost hour changing from daylight savings to standard time

From: Eric Tishkoff (EricTishkoff_at_discussions.microsoft.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 12:28:03 -0800

I was curious about what happens with time stamps (not the T-SQL datatype,
but rather saving absolute time of insert/modification) during the changeover
from daylight savings to standard time. Since we "fall back," the one o'clock
hour gets repeated, once under daylight savings and a second time under
standard time.

As an experiment, I set up a table called TEMP_TimeChange

CREATE TABLE [dbo].[TEMP_TimeChange] (
        [Id] [int] IDENTITY (1, 1) NOT NULL ,
        [ServerTime] [datetime] NULL ,
        [UTCTimeStamp] [datetime] NULL
) ON [PRIMARY]
GO

and job to run every five minutes starting at about 1:00 am (daylight
savings time) on Sunday morning. The job simply inserted a new record into
TEMP_TimeChange each time it ran.

        insert into TEMP_TimeChange (ServerTime) values (getdate())

I expected to see the ServerTime cycle through the one o'clock hour twice
like this:

ServerTime UTCTimeStamp
-------------------- --------------------
Oct 31 2004 1:45AM Oct 31 2004 8:45AM
Oct 31 2004 1:50AM Oct 31 2004 8:50AM
Oct 31 2004 1:55AM Oct 31 2004 8:55AM
Oct 31 2004 1:00AM Oct 31 2004 9:00AM
Oct 31 2004 1:05AM Oct 31 2004 9:05AM
Oct 31 2004 1:10AM Oct 31 2004 9:10AM
...
Oct 31 2004 1:45AM Oct 31 2004 9:45AM
Oct 31 2004 1:50AM Oct 31 2004 9:50AM
Oct 31 2004 1:55AM Oct 31 2004 9:55AM
Oct 31 2004 2:00AM Oct 31 2004 10:00AM
Oct 31 2004 2:05AM Oct 31 2004 10:05AM
Oct 31 2004 2:10AM Oct 31 2004 10:10AM

Instead, the job did not run the second time through the one o'clock hour
(as indicated by a one hour gap in the UTC time stamp). Here is the actual
result:

ServerTime UTCTimeStamp
-------------------- --------------------
Oct 31 2004 1:45AM Oct 31 2004 8:45AM
Oct 31 2004 1:50AM Oct 31 2004 8:50AM
Oct 31 2004 1:55AM Oct 31 2004 8:55AM
Oct 31 2004 2:00AM Oct 31 2004 10:00AM
Oct 31 2004 2:05AM Oct 31 2004 10:05AM
Oct 31 2004 2:10AM Oct 31 2004 10:10AM

The interesting/concerning thing is that the job did not run for an hour.
The Windows task scheduler appears to have worked the same way as evidenced
by the numerous pages our ops people received starting a few minutes after
1:00 am standard time. The concern is that critical jobs don't run. For
example, We have a task that backs up the transaction log every 10 mins. It
did not run at all during that hour. If we had experienced a severe database
problem, at 1:55 am standard time, we would have lost all new data for the
prior 65 minutes, dating back to the last transatcion log backup that
occurred at 1:50 am daylight savings time.

To couch this as a question, is there MS documentation that discusses this
issue and/or work arounds to ensure that critical jobs run as expected?

et

Relevant Pages

  • Re: Reminder Daylight Savings Time This Week-end
    ... last year, whilst New South Wales (NSW), The Australian Capital Territory ... the Northern Territory don't do daylight savings. ... being Australian Western Standard time. ...
    (rec.pets.cats.anecdotes)
  • Re: DateTime and timezone.
    ... Are you sure you're using "Mountain Standard Time" and not "US Mountain ... > SystemTimeToTzSpecificLocalTime - I pass the system time that I received ... >> You should get the daylight savings time if it's in effect. ...
    (microsoft.public.dotnet.framework)
  • Re: Calendar Events - Time Problem
    ... and Entourage are both set for Eastern Standard Time. ... You're making events to occur next week or later - in Daylight Savings Time ... first day of EDT when the time changes after the day begins. ... Entourage FAQ Page: ...
    (microsoft.public.mac.office.entourage)
  • Re: Determining Daylight Savings
    ... Daylight savings starts on the last Sunday in October and Standard time starts on the 1st Sunday in April so... ... Dim intLastSunday As Integer ... Dim intFirstSunday As Integer ...
    (microsoft.public.access.formscoding)
  • Daylight savings time and external hard drives
    ... Each fall and winter I deal witht he same problem. ... time comes into effect the time stamp for files on my external portable hard ... drive are 1 hour out from the time stamps for the files on my Desktop hard ... desktop the morning after daylight savings time has taken effect, ...
    (microsoft.public.dotnet.general)