10.6.2. Time Zone Leap Second Support

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. The following example demonstrates this. It changes the local time zone to UTC so there is no difference between internal values (which are in UTC) and displayed values (which have time zone correction applied).

mysql> CREATE TABLE t1
        (    ->  a INT,    ->  ts TIMESTAMP DEFAULT NOW(),    ->  PRIMARY KEY (ts)    -> );Query OK, 0 rows affected (0.01 sec)mysql> -- change to UTCmysql> SET
        time_zone = '+00:00';Query OK, 0 rows affected (0.00 sec)mysql> -- Simulate NOW() = '2008-12-31 23:59:59'mysql> SET timestamp = 1230767999;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO t1 (a) VALUES (1);Query OK, 1 row affected (0.00 sec)mysql> -- Simulate NOW() = '2008-12-31 23:59:60'mysql> SET timestamp = 1230768000;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO t1 (a) VALUES (2);Query OK, 1 row affected (0.00 sec)mysql> -- values differ internally but display the samemysql> SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;+------+---------------------+--------------------+| a    | ts                  | UNIX_TIMESTAMP(ts) |+------+---------------------+--------------------+|    1 | 2008-12-31 23:59:59 |         1230767999 ||    2 | 2008-12-31 23:59:59 |         1230768000 |+------+---------------------+--------------------+2 rows in set (0.00 sec)mysql> -- only the non-leap value matchesmysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:59';+------+---------------------+| a    | ts                  |+------+---------------------+|    1 | 2008-12-31 23:59:59 |+------+---------------------+1 row in set (0.00 sec)mysql> -- the leap value with seconds=60 is invalidmysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';Empty set, 2 warnings (0.00 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> -- selecting using UNIX_TIMESTAMP value return
        leap valuemysql> SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) =
        1230768000;+------+---------------------+| a    | ts                  |+------+---------------------+|    2 | 2008-12-31 23:59:59 |+------+---------------------+1 row in set (0.00 sec)