Dec
01
2008
MySQL and the leap second on December 31 2008
Posted by Peter Gulutzan on Mon 01 Dec 2008 18:11 UTC
Tags:

“Leap seconds” are starting to cause questions on our forums and in some bug reports because ‘2008-12-31 23:59:60′ will be the first leap second in the last 3 years. We have a short answer and a long answer.

The short answer is: it will only cause a problem if you
(
(
(a) arrange so your operating system returns the leap-second value for MySQL to see,
or
(b) load the appropriate ‘leap second’ tables as described in our manual,
)
and
(c) do something which uses the NOW() or CURRENT_TIMESTAMP at precisely midnight UTC on December 31 (remember your local time is not UTC time!).
)
or
use CONVERT_TZ() or FROM_UNIXTIME().

Even if those unlikely things occur, you can kludge the results next day. So don’t worry. My colleague Dmitri Lenev points out: there are leap -second geeks who are configuring their systems to have non-posix unix time/zoneinfo with leap seconds included and only they are affected.

The long answer, for the people who insist on worrying anyway or who are just curious about leap seconds in SQL and/or MySQL, is the rest of this article.

GENERAL INFORMATION

If you haven’t heard about leap seconds before, try these articles first:

http://www.ucolick.org/~sla/leapsecs/HTMLutcdoomed.html
(My favourite line from this is: the rotating earth is decelerating at a rate of 1.7 milliseconds per day per century, but not in a regular way.

http://www.airspacemag.com/flight-today/One-More-Second.html
(My favourite line from this is: leap seconds are affected by “thinning of glaciers caused by global warming”, among other things.)

http://kbase.redhat.com/faq/docs/DOC-15153
(My favourite line from this is: Red Hat handles leap seconds by making ‘23:59:59′ twice as long, which is inspiring. But I don’t have information about every Linux installation. Ask your OS vendor.)

My colleague Magne Mæhre points out: ” mostly it’s due to a sloppy estimation of the natural frequency of the caesium atom. When the SI second was introduced, it was defined to be the duration of 9 192 631 770 cycles of the radiation (…) of the Cs-133 atom. The problem is that this has nothing to do with the rotation of the earth If the second had been defined as 9 192 631 997 periods instead, we would only have had three leap seconds (2 positive and 1 negative) since 1972, instead of the 22 (all positive) we’ve had.”

LOOK AT YOUR MYSQL INSTALLATION

For some platforms, MySQL supplies “Time zone description tables” http://dev.mysql.com/downloads/timezones.html

If you didn’t upload a time-zone and leap-second table, then you’re looking at a case for which there’s no reported problem.

To find out what’s in your leap-second table, say:
select transition_time,from_unixtime(transition_time),correction
from mysql.time_zone_leap_second;
If the result looks like this: 

+-----------------+--------------------------------+------------+
| transition_time | from_unixtime(transition_time) | correction |
+-----------------+--------------------------------+------------+
|        78796800 | 1972-06-30 18:00:00            |          1 |
|        94694401 | 1972-12-31 17:00:01            |          2 |
...
|       867715220 | 1997-06-30 18:00:20            |         21 |
|       915148821 | 1998-12-31 17:00:21            |         22 |
|      1136073622 | 2005-12-31 17:00:22            |         23 |
+-----------------+--------------------------------+------------+
23 rows in set (0.00 sec)

then your table has not been updated for the leap second.

SUPPORT OF LEAP SECONDS BY OTHER DBMS VENDORS

Oracle won’t accept a timestamp literal with a leap second. I tried
insert into t values (timestamp ‘2005-12-31 23:59:60′);
The result was “ORA-1852: seconds must be between 0 and 59″. That’s not an abnormal limitation. The SQL Server 2008 manual says:
“Using hour 24 to represent midnight and leap second over 59 as defined by ISO 8601 (5.3.2 and 5.3) are not supported to be backward compatible and consistent with the existing date and time types. They are not defined by SQL standard 2003.”
http://msdn.microsoft.com/en-us/library/bb677243.aspx
I only found one vendor document which clearly claims support for leap seconds: for Informix:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.docnotes.doc/xc8w4/ids_sqls_docnotes_10.0.html

STANDARD SQL

Microsoft isn’t telling the whole story when it says “not defined by SQL standard 2003″. The SQL standard doesn’t define, but it allows:
“… sometimes, but very rarely, a particular minute will contain exactly 59, 61, or 62 seconds. Whether an SQL-implementation supports leap seconds, and the consequences of such support for date and interval arithmetic, is implementation-defined.”
In another place, it says that valid values for the SECOND field in a datetime are between 00 and 61.9. It’s unfortunately true that when talking about casting to INTERVAL targets the standard says
“according to the natural rules for intervals as defined in the Gregorian calendar (that is, there are 60 seconds in a minute”.
Well, Pope Gregory XIII would be surprised to hear that he said that, but that’s only about normalizing interval literals, we can ignore it.

TIME ZONE AGNOSTICISM

According to Wikipedia. Leap seconds occur “simultaneously worldwide; for example, the leap second on December 31, 2005 occurred at 23:59:60 UTC. This was 6:59:60 p.m. U.S. Eastern Standard Time and 0:59:60 a.m. on January 1, 2006 Central European Time.” Therefore MySQL might not always say that ‘22:59:60′ is invalid.
But these things are still invalid:
* (datetime/timestamp < ‘2009-05-31′
and (month,day) not between ‘12-31 11:00:00′ and 01-01 13:00′)
and (month,day) not between ‘05-31 11:00:00′ and 06-01 13:00′))
or datetime/timestamp < ‘1971-12-31′
* datetime/timestamp < ‘2009-05-31′
and second > 60 /* because there has never been a 2-second jump */
* MINUTES not in (44,59,14,29)
and second > 59 /* because zone offsets like ‘12 minutes’ don’t occur */
* datetime/timestamp in UTC doesn’t end in 23:59:60′
and second > 59 /* if user actually specifies a time zone */
None of the above checks depend on actual access to time zone tables. Therefore, getting invalid data in is hard, except via NOW() or CURRENT_TIMESTAMP at the aforementioned moment.

INTERVAL ARITHMETIC

Currently I get this: 

mysql> select timestampdiff(second,'2009-01-01 00:00:00','2008-12-31 23:59:59');
+-------------------------------------------------------------------+
| timestampdiff(second,'2009-01-01 00:00:00','2008-12-31 23:59:59') |
+-------------------------------------------------------------------+
|                                                                -1 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

Some might say that it should be -2, or at least Bug#40610 “Date and time functions like DATE_SUB(), DATE_ADD() are not leap second enabled.” said o, because -1 is “not what people expect”.

I disagree for these reasons:
* I’m not so sure “what people expect”.
* The effect will be: if you have an up-to-date leap seconds table,
you’ll get -2, but otherwise you’ll get -1. This could hurt replication
more than it helps.

Therefore I claimed that we should dismiss bug#40610 as an unreasonable feature request, ignore leap seconds in all interval calculations, and say that ‘2008-12-31 23:59:60′ = ‘2008-12-31 23:59:59′. It turned out there’s an even easier solution, though.

MAKETIME

Currently I get this: 

mysql> select maketime(23,59,60);
+--------------------+
| maketime(23,59,60) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

In other words, MAKETIME treats ‘23:59:60′ as invalid.

STR_TO_DATE

Currently I get this: 

mysql> SELECT STR_TO_DATE('2008-12-31 23:59:60', '%Y-%m-%d %H:%i:%S');
+---------------------------------------------------------+
| STR_TO_DATE('2008-12-31 23:59:60', '%Y-%m-%d %H:%i:%S') |
+---------------------------------------------------------+
| NULL                                                    |
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

In other words, STR_TO_DATE treats ‘23:59:60′ as invalid.

UNIX_TIMESTAMP

Currently I get this: 

mysql> select unix_timestamp('2008-12-31 23:59:60');
+---------------------------------------+
| unix_timestamp('2008-12-31 23:59:60') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

I think we could have accepted that unix_timestamp(’2008-12-31 23:59:60′) would return different values on different systems. If there is a leap second table available, then the response could be the ‘:60′ value. If there is no leap second table available, then the response should not be 0, it should be the same as for 2008-12-31 23:59:59. in my opinion.
But it’s not. Oh, well.

UPDATING MYSQL.TIME_ZONE_LEAP_SECOND TABLE

For some platforms, MySQL supplies “Time zone description tables”
http://dev.mysql.com/downloads/timezones.html
Probably, at time of reading, your copy is not up to date! 2008-12-31 is not there! Also I expect that operating-system vendors’ tables are not up to date, or else that users haven’t updated lately. A similar thing happened with the time-zone tables last year, when some countries decided to change the dates that daylight saving time begins and ends.
I’d say it’s up to each user to check for update bulletins every six months from http://hpiers.obspm.fr/iers/bul/bulc/.

CONNECTORS

I am reasonably confident that there would be no problems moving ‘2008-12-31 23:59:60′ back and forth from C and Java programs, although I suppose somebody should ask if there is any JDBC problem retrieving completely invalid values like ‘2008-12-01 23:45:61′. And I have noticed that some common C structures e.g. in time.h
http://www.uwm.edu/cgi-bin/IMT/wwwman?topic=ctime(3)&msection= say that the maximum number of seconds is 60, not 61. MySQL should be rejecting such things, but I can’t be sure of every possible case.

STORAGE ENGINES

We know of no problems with our own storage engines. In theory there could be a storage engine which stores dates and datetimes in a compressed form that doesn’t allow for 62 seconds per minute.

EVENTS

An event created with
create event e on schedule every 1 second starts ‘2008-12-31 23:59:59′ do set @a=5;
should happen at 23:59:59 but not at 23:59:60.

A GENERAL OBSERVATION

My colleague Georgi Kodinov points out: in MySQL date arithmetic in general (when using DATE/TIME/DATETIME columns) is done according to our internal understanding of the UTC time zone (see Bug#40329 “TIMESTAMPADD does not take in to account Daylight Savings Time”). If you need time-zone dependent calculations you’ll need to convert to local time, do the math and then convert back it seems.

THE FIX

As a fix, we’re supplying a patched version of the MySQL server. Its NOW() function will return 23:59:59 twice on December 31. That’s not exactly the right answer, but it sure saves a lot of trouble.

You can get this fix as a source code patch now, just look at the comments for Bug#39920 “MySQL cannot deal with Leap Second expression in string literal.” I’m hoping that MySQL will put this in binaries soon, but I don’t guarantee such things.

The alternative is to do an “after the fact” patch when you head into the office on January 1 2009. You can find and get rid of the value in affected columns thus:
UPDATE affected_table
SET affected_column = CONCAT(LEFT(affected_column,LENGTH(affected_column)-2),’59′)
WHERE RIGHT(affected_column,2) = ‘60′;

REFERENCES

Bug#6387 Queried timestamp values do not match the inserted value (closed in 2004)
Bug#18883 Error fetching TIME columns > 23:59:59
Bug#39920 MySQL cannot deal with Leap Second expression in string literal.
Bug#40610 Date and time functions like DATE_SUB(), DATE_ADD() are not leap second enabled.
“RE: leap second” http://lists.mysql.com/internals/36100