Bug #76523 unrepeatable example in the doc on leap second support

• Submitted: 30 Mar 2015 1:26
• Modified: 30 Mar 2015 5:38
• Reporter: Peiran Song
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[30 Mar 2015 1:26] Peiran Song

Description:
The example in the following documentation page is not repeatable:

http://dev.mysql.com/doc/refman/5.6/en/time-zone-leap-seconds.html

Per the documentation, unix_timestamp 1230768000 corresponds to the leap second 2008-12-31 23:59:60, however, in my test, it corresponds to 2009-01-01 00:00:00. 

From the documentation, it sounds like there is a unix timestamp for the leap second, but it wasn't demonstrated with the example. I also tried 2012-06-30 23:59:60, and upcoming 2015-06-30 23:59:60 and there doesn't seem to be a unix timestamp for either leap second. Not sure if I am missing something. I am wondering, at the leap second, what would now() return? 

How to repeat:
5.6.19

mysql> CREATE TABLE t1 ( a INT,ts TIMESTAMP DEFAULT NOW(),PRIMARY KEY (ts));
Query OK, 0 rows affected (0.08 sec)

mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SET timestamp = 1230767999;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.02 sec)

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

mysql>  SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;
+------+---------------------+--------------------+
| a    | ts                  | UNIX_TIMESTAMP(ts) |
+------+---------------------+--------------------+
|    1 | 2008-12-31 23:59:59 |         1230767999 |
|    2 | 2009-01-01 00:00:00 |         1230768000 |
+------+---------------------+--------------------+
2 rows in set (0.00 sec)

mysql> select from_unixtime(1230768001);
+---------------------------+
| from_unixtime(1230768001) |
+---------------------------+
| 2009-01-01 00:00:01       |
+---------------------------+

[30 Mar 2015 5:38] MySQL Verification Team

Hello Peiran Song,

Thank you for the report.
I observed similar results(as shown in report) from 5.0.96, 5.1.73, 5.5.44, 5.6.25 and 5.7.8.

Thanks,
Umesh

[31 Mar 2015 13:23] Jon Stephens

mysql> select unix_timestamp("2008-12-31 23:59:59");
+---------------------------------------+
| unix_timestamp("2008-12-31 23:59:59") |
+---------------------------------------+
|                            1230767999 |
+---------------------------------------+
1 row in set (0.00 sec)

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

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2008-12-31 23:59:60' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("2009-01-01 00:00:00");
+---------------------------------------+
| unix_timestamp("2009-01-01 00:00:00") |
+---------------------------------------+
|                            1230768000 |
+---------------------------------------+
1 row in set (0.00 sec)

We don't document misbehaviour. Misbehaviour is a software bug, and needs to be fixed.