Storing a leap second in SQL Server 2008

Question

This weekend is an extra long one as there will be an extra second inserted after 23:59:59 on June 30th.

We have a system that logs a lot of data around the clock and one of the business rules is that no two records can be logged as having occurred at the same time, to within one second.

We're using UTC datetimes along with the new datetimeoffset data type, but as far as I can tell they won't let you have more than 60 seconds in a minute.

Certainly, this throws an error:

select datediff(ss, getdate(), '30-jun-2012 23:59:60')

But according to the UTC gods this will be a real time. Events can take place at 23:59:60 but we have no way of recording this fact.

23:59:59 plus one second offset will still be considered 00:00:00 on July 1st.

How can I correctly log that an event occurred at 23:59:60 in the database?

Answer 1

You cannot, because SQL gets the time from Windows, and Windows doesn't support leap seconds either.

Windows applies leap seconds by taking the new time from the upstream time server, and applying the usual adjustments as if it were simple clock drift.

Usually this means adjusting each second by a few nanoseconds over an extended period. Over 24 hours it would work out at about one millisecond per minute.

• How the Windows Time service treats a leap second: http://support.microsoft.com/kb/909614
• Does the windows FILETIME structure include leap seconds?
• How the Windows Time Service Works: http://technet.microsoft.com/en-us/library/cc773013(v=ws.10).aspx

Basically, most applications simply pretend that there is no such thing as leap seconds.

For most purposes this doesn't matter. If you have an application where this matters, the OS will not help you. You will also need some special hardware for tracking time, as OS's generally have trouble keeping time to within a second anyway. Windows by default synchronises time weekly or less often, and most cheap PC hardware clocks (or even those in expensive servers) can easily drift several seconds in that time.

Since you do care about the exact time, I assume you are pointing at pool.ntp.org or your regional subnet and have set w32time for synchronisation several times per day.