12

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Widor
  • 13,003
  • 7
  • 42
  • 64
  • 4
    It appears your business rules ignore reality. – Kevin P. Rice Jul 13 '12 at 23:49
  • 1
    How so? The events being logged can't physically occur more than once a second. I wasn't aware you were working on the same project either. – Widor Jul 14 '12 at 20:22
  • 1
    I was speaking in jest, but the reality is the system cannot store a time stamp for the leap second, thus is is not possible to log one event per second during a leap second. Business rules are requiring something that cannot be easily met through traditional means. Rethink rules, make exception for leap seconds, or write crazy one-off code. – Kevin P. Rice Jul 15 '12 at 00:44
  • Crazy one-code off sounds attractive! :-D – Prof. Falken Aug 23 '12 at 07:10

1 Answers1

9

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.

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.

Community
  • 1
  • 1
Ben
  • 34,935
  • 6
  • 74
  • 113
  • Be aware that by adapting the time at the next ntp synchronisation, your clock will slow down to let the future integrate in your system :) By the way, when your clock is too far off, it will be set to the correct time, thus causing the same problem with duplicate entries in your database. MSDN: "[...adjusts the clock rate ... to allow it to converge toward the correct time. If the time difference ... is too large ... the time service sets the local clock...](https://technet.microsoft.com/en-us/library/cc773013(v=ws.10).aspx#w2k3tr_times_how_izcr)" – eFloh Jun 30 '15 at 10:56