summertime/wintertime change

[bjoern6689]

The time-shift from summertime to wintertime is comming up.
Is that a problem from SQL-server 7 or SQL-server 2000 ?

 

[Kolt]

it depends on Windows settings : Control Panel->Date/Time->Time Zone->automaticly ajust...

 

[bjoern6689]

Sorry what I ment was: Is SQL-server able to handle the fact that i.e. the time 02:20 occurs twice?

 

[JayKusch]

Unfortunately, it does not. We have the same issue with transactional data that is tracked w/ a timestamp. The way we are able to get around it is by turning off the OS system function that takes care of daylight savings time and then during our 1 hour of downtime, later that morning, we send out an SP to update the time to the new time.

This sure becomes an interesting issue for 24/7 shops!

Thanks

J. Kusch

 

[JayKusch]

As a side note, I have heard of a workaround where you add a field to the table(s) that would be effected by this issue, say a bit field.

Once the time change happens, for the first hour of "new" transactions that are captured between 2:00:00.000 to 2:59.59.999, you set that bit flag to 1.

Now you are able to differentiate between the first set of 2am to 3am set of transactions from the new "daylight savings" set of transactions.

Of course, if there are a bunch of SPs that create this data, it can be a daunting task.

Thanks

J. Kusch

 

[wyldsider]

What we have done here to get around the problem is to store the UTC (Universal Time) itself, and then using the windows routines to translate into the local times. Now, we enter most of our times manually, so this is an option to us.

Is there a way to tell SQL Server you want to add a UTC time to the time field instead of a local time?

cheers,
Jack

Jack De Winter
Software Developer
MedTel Software

http://www.medtel.on.ca

 

[JayKusch]

You could use the GetUTCDate() Function.

Thanks

J. Kusch