Before MySQL 5.1.31, if the operating system is configured to
return leap seconds from OS time calls or if the MySQL server
uses a time zone definition that has leap seconds, functions
such as NOW()
could return a
value having a time part that ends with
:59:60
or :59:61
. If such
values are inserted into a table, they would be dumped as is by
mysqldump but considered invalid when
reloaded, leading to backup/restore problems.
As of MySQL 5.1.31, leap second values are returned with a time
part that ends with :59:59
. This means that a
function such as NOW()
can return
the same value for two or three consecutive seconds during the
leap second. It remains true that literal temporal values having
a time part that ends with :59:60
or
:59:61
are considered invalid.
If it is necessary to search for
TIMESTAMP
values one second
before the leap second, anomalous results may be obtained if you
use a comparison with 'YYYY-MM-DD hh:mm:ss'
values:
mysql>CREATE TABLE t1 (a INT, ts TIMESTAMP DEFAULT NOW(), PRIMARY KEY (ts));
Query OK, 0 rows affected (0.11 sec) mysql># Simulate NOW() = '2009-01-01 02:59:59'
mysql>SET timestamp = 1230768022;
Query OK, 0 rows affected (0.00 sec) mysql>INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.07 sec) mysql># Simulate NOW() = '2009-01-01 02:59:60'
mysql>SET timestamp = 1230768023;
Query OK, 0 rows affected (0.00 sec) mysql>INSERT INTO t1 (a) VALUES (2);
Query OK, 1 row affected (0.02 sec) mysql>SELECT * FROM t1;
+------+---------------------+ | a | ts | +------+---------------------+ | 1 | 2008-12-31 18:00:22 | | 2 | 2008-12-31 18:00:23 | +------+---------------------+ 2 rows in set (0.02 sec) mysql>SELECT * FROM t1 WHERE ts = '2009-01-01 02:59:59';
Empty set (0.03 sec)
To work around this, you can use a comparison based on the UTC value actually stored in column, which has the leap second correction applied:
mysql> SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) = 1230768023;
+------+---------------------+
| a | ts |
+------+---------------------+
| 2 | 2008-12-31 18:00:23 |
+------+---------------------+
1 row in set (0.02 sec)
User Comments
Add your own comment.