跨越数据库的第二次处理 [英] Leap second handling in database

问题描述

作为:

The Unix time number is zero at the Unix epoch, and increases by exactly 86400
per day since the epoch. So it cannot represent leap seconds. The OS will slow 
down the clock to accomodate for this. 

那么，如果我将Unix纪元(例如ts)存储在DB中(毫秒级精度)，该如何处理以下情况?

So, if I am storing Unix epoch (e.g. ts) in DB (milli-second accuracy), how to handle the following case?

1. 如何确保ts一直在增加并且没有向后移动?
2. 如何准确地从db中选择100秒间隔，并考虑到the秒?

例如

SELECT * FROM events WHERE ts >= T1 and ts < T1 + 100

上面的SQL将返回在T1，T1 + 1，T1 + 2，..直到T1 + 99发生的事件，但是由于leap秒，包括1s的跳跃时间，结果可能是错误的，如何考虑到这一点?

The above SQL will return events which happen at T1, T1+1, T1+2, ..up to T1+99, but since due to leap seconds, the result might be wrong by including the leap time of 1s, how to take account into this?

推荐答案

我将首先说我在现实生活中没有遇到过这样的问题，所以我只会猜测，但这将是有教养的猜测.根据 http://en.wikipedia.org/wiki/Unix_time#Encoding_time_as_a_number second秒插入问题是2次(例如1998-12-31T23:59:60.00和1999-01-01T00:00:00.00)具有相同的Unix时间(915.148.800.000).删除a秒后，应该不会有任何问题.

I'm going to begin by saying i haven't been confronted with such a problem in real life so i'll only guess but it will be an educated guess. According to http://en.wikipedia.org/wiki/Unix_time#Encoding_time_as_a_number when a leap second is inserted the problem is 2 times (e.g. 1998-12-31T23:59:60.00 and 1999-01-01T00:00:00.00) have the same Unix time (915.148.800.000). When a leap second is deleted there shouldn't be any problem.

根据同一Wikipedia页上的注释#2，leap秒是不可预测的，这给您留下了两个选择:一种通用解决方案(假设您已将这些时间戳索引到表中)可以始终插入条目，而在插入条目的那一刻如果发生在最后插入的条目之前(可能在a秒内)，则可以开始涂片"过程，该过程基本上是在条目上增加几毫秒的时间，以确保它不在the秒的范围内.当插入的条目再次具有比先前插入的条目更大的值时，该过程可以停止.我将其称为涂片"是因为它在某种程度上受到Google的飞跃涂片"技术的启发(尽管并不完全相同):

Acording to Note #2 on the same Wikipedia page leap seconds are not predictable, which leaves you with 2 choices: a generic solution which (assuming you have the table indexed by these timestamps) can always insert entries and the moment one entry occurs prior to the last inserted entry (probably within a leap second) you can begin a 'smear' process which is basically adding some milliseconds to the entry to make sure it falls out of the range of the leap second. The process can stop the moment the inserted entry will again have a bigger value than the previously inserted entry. I call this 'smear' because it's somehow inspired by Google's "Leap Smear" technique (although not quite the same): http://googleblog.blogspot.in/2011/09/time-technology-and-leaping-seconds.html The way i see it though this can put some strain on your database and that insertion query would just about be one of the most complex queries i've seen (if it even is possible in SQL alone).

另一种解决方案是(假设您使用的是Java)，您可以手动检查时间戳是否在a秒以内.如果是这样，只需阻止对数据库的任何访问并将条目插入队列.当the秒结束时，只需以FIFO方式将队列插入数据库中即可保证您要关心的顺序(类似于上述解决方案，但完全使用Java，因此甚至没有涉及到DB层).您可以通过消除队列并将其直接插入数据库中来进行一些优化，就像上面一样，只需一秒钟就涂抹"条目.

Another solution can be (i'm assuming you're using Java) that you manually check whether the timestamp falls within a leap second or not. If it does, just block any access to the database and insert the entries into a queue. When the leap second is over just insert the queue in FIFO manner into the database to guarantee the order you care for (similar to the solution above but entirely in Java, so before it even touches the DB layer). You can optimize this a bit by eliminating the queue and insert directly into the DB - just 'smear' the entries over one second like above.

当然，缺点是您在you秒上牺牲了一些准确性(考虑leap秒的情况下，不会有很大的牺牲是很少见的)，但优点是它很简单并且可以保证您的订单.

Of course there is the downside that you sacrifice a bit of accuracy in that leap second (not a big sacrifice considering leap seconds are so rare) but the plus is that it's simple and your order is guaranteed.

如果您或其他任何人找到更好的解决方案，请在此处分享，这个话题很有趣:)

If you or anyone else finds better solutions please share them here, this topic is pretty interesting :)

更新:我已经为第三种解决方案编写了伪代码(完全在SQL查询中)，该解决方案依赖于经过硬编码检查的a秒(比通用解决方案要快).它可能可以进行很多优化，但只是为了证明我的观点:

Update: i've written the pseudocode for a 3rd solution (entirely in the SQL query) which relies on a hardcoded check for a leap second (faster than a generic solution). It can probably be optimized a lot but just to prove my point:

if (newTime is in a leap second){
    read smearCount from db;
    if (smearCount <= 0) {
        smearCount = 1000; // making sure we land outside the leap second
        update smearCount in db;
    }
    newTime += smearCount;
    insert newTime into db;
} else { // gradually reducing smearCount by 1 millisecond over the following 1000 insertions
    read smearCount from db;
    if (smearCount > 0){
        smearCount -= 1;
        update smearCount in db;
        newTime += smearCount;
    }
    insert newTime into db;
}

这篇关于跨越数据库的第二次处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！