在SQL Server 2008中存储闰秒 [英] Storing a leap second in SQL Server 2008

问题描述

这个周末是一个很长的时间，因为将会插入一个额外的第二个 23:59:59 在6月30日。

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.

我们正在使用UTC数据时间新的 datetimeoffset 数据类型，但据我所知，他们不会让你在一分钟内超过60秒。

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.

当然这会引发错误： p>

Certainly, this throws an error:

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

但是根据UTC神，这将是一个实时的。事件可以在 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 加上一秒钟的偏移仍将被视为 00:00 ：00 7月1日。

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

如何正确记录事件发生在 23:59:60 在数据库中？

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

推荐答案

你不能，因为SQL从Windows获取时间， t支持闰秒。

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

Windows通过从上游时间服务器获取新的时间来应用闰秒，并应用通常的调整，就好像简单时钟漂移。

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.

通常这意味着在延长的时间内每秒调整几纳秒。超过24小时，它将以每分钟约一毫秒的速度运行。

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.

• Windows时间服务如何处理闰秒： http://support.microsoft.com/kb/909614


• < a href =http://stackoverflow.com/questions/130573/does-the-windows-filetime-structure-include-leap-seconds> Windows FILETIME结构是否包含闰秒？


• Windows时间服务的工作原理： http://technet.microsoft.com/en-us/library/cc773013（v = ws.10）.aspx

• 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.

大多数情况下，这并不重要。如果您有一个这样的应用程序，操作系统将不会帮助您。您还需要一些特殊的硬件跟踪时间，因为操作系统通常无法保持时间到一秒钟内。 Windows默认每周或更少的时间同步时间，最便宜的PC硬件时钟（或甚至昂贵的服务器）可以轻松漂移几秒钟。

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.

自从你请注意确切的时间，我假设您指向pool.ntp.org或您的区域子网，并且每天设置w32time进行同步多次。

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.

这篇关于在SQL Server 2008中存储闰秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！