Leap second handling in database

Question

As:

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. 

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

1. How to make sure the the ts is always increasing and no backward?
2. How to select exactly the 100s interval from db which take account into the leap second?

e.g.

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

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?

Answer 1

From the Joda Time FAQ:

Are leap seconds supported?

Joda-Time does not support leap seconds. Leap seconds can be supported by writing a new, specialized chronology, or by making a few enhancements to the existing ZonedChronology class. In either case, future versions of Joda-Time will not enable leap seconds by default. Most applications have no need for it, and it might have additional performance costs.

From the IANA/Olson TZDB file on leap seconds:

Although the definition also includes the possibility of dropping seconds ("negative" leap seconds), this has never been done and is unlikely to be necessary in the foreseeable future.

Your first question:

How to make sure the the ts is always increasing and no backward?

One negative leap second would leave you at the same timestamp (one value for two elapsed seconds), so you can't really go backward without two negative leap seconds. Since it doesn't appear likely that there will ever be a negative leap seconds, I'd say this is a problem you won't ever really encounter.

Update: The only way I can envision the timestamps going backwards is if you use millisecond precision and encounter behavior #3 below.

Your second question:

How to select exactly the 100s interval from db which take account into the leap second?

Since your are recording times in UTC, your values are already inclusive of leap seconds. I know that may sound counter-intuitive, since as you described there are exactly 86400 seconds (86400000 ms) in a day by this scale. But they are indeed in there. If they weren't - then we would be synchronizing with TAI, not UTC. So how can that be? Well, there are a few different things that can happen when a leap second occurs:

1. If leap seconds are supported by both the operating system and the application code, then it could indeed show a show seconds as :60 or :61. But there are almost no true implementations of this because programming languages usually only allow for seconds going to :59.

2. The operating system might "freeze" for a second, giving the same value for a full second.

3. The operating system might advance to :59.999, and then jump back to :59.000 to repeat the period covered by the leap second. (thanks @Teo)

4. The operating system might "drift" or "smear" for awhile, slowly adding a few milliseconds at a time to the system clock, until it has fully caught up with the extra second.

5. The operating system might skip right over it and do nothing. Your clock would be out of sync until the next time it synced via NTP. And if it happened to sync right at the moment of the leap second, it will probably just set the time to :59 or :00 and again be out of sync for awhile.

Let's consider a real example. The value 1341100800000 represents July 1st 2012 exactly at midnight UTC. (You can check it on this web site, or in your in your Java or Joda Time code. to verify.) If we divide by 86400000, we'll get exactly the 15522 days that have elapsed since 1/1/1970 UTC.

This value is inclusive of the 35 leap seconds, including the one that happened just one second prior at the end of day on June 30th 2012. It's as if the leap seconds never happened at all.

So most of the time, you don't need to worry about leap seconds. Pretend they don't exist. Let your operating system handle them in whichever way it wants to.

If you require ultra-precise time measurements, perhaps in a scientific context, then you shouldn't be using the computer's system clock anyway. Besides the fact that the leap second could be held, stretched, or ignored, it's just not designed to be that precise of a timer. Instead, you should probably be working with some very specialized timekeeping hardware, such as those offered by this vendor.

Update: Where you might need to deal with leap seconds is if you are recording events rapidly (many events per second), and if your operating system has the behavior described in #3 above. In that case, my recommendation would be to not sort by timestamp, but instead consider keeping a separate monotonically increasing sequence number of your own, and sort by that instead.

For example, you might already have an auto-incrementing integer ID in your database. You could still filter by timestamp in your where clause to get data for a particular date, but you would then order by the ID so the events where in sequence even if the timestamps were not.

For other suggestions, see Teo's answer.

Answer 2

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.

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

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.

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 :)

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;
}