In MySQL 5.5, 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.