Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.0(EOL)
-
None
-
None
-
None
Description
Observing leap second is not trivial so here's a complete howto:
(Thanks to bar for help and discussion btw!)
Get a time zone with leap second:
./sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/right/Europe/London right/Europe/London > london1.sql
|
./sql/mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/right/Europe/London > london2.sql
|
Load it into server
use mysql;
|
source london1.sql
|
source london2.sql
|
Restart the server. This is important.
Check that you can observe leap second:
set time_zone='right/Europe/London';
|
select from_unixtime(1483228825), from_unixtime(1483228826);
|
+---------------------------+---------------------------+
|
| from_unixtime(1483228825) | from_unixtime(1483228826) |
|
+---------------------------+---------------------------+
|
| 2016-12-31 23:59:59 | 2016-12-31 23:59:59 |
|
+---------------------------+---------------------------+
|
1 row in set (0.001 sec)
|
Let's create a table with a TIMESTAMP column and row with the leap second.
CREATE TABLE t1 (
|
a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
key(a)
|
);
|
 |
set timestamp=1483228823;
|
insert into t1 values ();
|
set timestamp=1483228824;
|
insert into t1 values ();
|
set timestamp=1483228825;
|
insert into t1 values ();
|
set timestamp=1483228826;
|
insert into t1 values ();
|
set timestamp=1483228827;
|
insert into t1 values ();
|
MariaDB [test]> select * from t1;
|
+---------------------+
|
| a |
|
+---------------------+
|
| 2016-12-31 23:59:57 |
|
| 2016-12-31 23:59:58 |
|
| 2016-12-31 23:59:59 |
|
| 2016-12-31 23:59:59 |
|
| 2017-01-01 00:00:00 |
|
+---------------------+
|
5 rows in set (0.009 sec)
|
Rows 3 and 4 look the same when converted into DATETIME, but internally they are not:
MariaDB [test]> select distinct a from t1;
|
+---------------------+
|
| a |
|
+---------------------+
|
| 2016-12-31 23:59:57 |
|
| 2016-12-31 23:59:58 |
|
| 2016-12-31 23:59:59 |
|
| 2016-12-31 23:59:59 |
|
| 2017-01-01 00:00:00 |
|
+---------------------+
|
5 rows in set (0.002 sec)
|
What happens if one uses range access?
MariaDB [test]> select * from t1 where a<='2016-12-31 23:59:59';
|
+---------------------+
|
| a |
|
+---------------------+
|
| 2016-12-31 23:59:57 |
|
| 2016-12-31 23:59:58 |
|
| 2016-12-31 23:59:59 |
|
+---------------------+
|
3 rows in set (0.002 sec)
|
MariaDB [test]> explain select * from t1 where a<='2016-12-31 23:59:59';
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0.002 sec)
|
MariaDB [test]> select * from t1 use index() where a<='2016-12-31 23:59:59';
|
+---------------------+
|
| a |
|
+---------------------+
|
| 2016-12-31 23:59:57 |
|
| 2016-12-31 23:59:58 |
|
| 2016-12-31 23:59:59 |
|
| 2016-12-31 23:59:59 |
|
+---------------------+
|
4 rows in set (0.001 sec)
|