| Bug #68832 | Daylight Saving time and interval | ||
|---|---|---|---|
| Submitted: | 2 Apr 2013 11:04 | Modified: | 26 Apr 2013 13:58 |
| Reporter: | Vincent Keravec | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.1.70 | OS: | Linux (RHEL6) |
| Assigned to: | CPU Architecture: | Any | |
[26 Apr 2013 13:58]
Erlend Dahl
Thank you for the bug report. It seems to be a long-standing problem, since I can repeat it in latest 5.1.70:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.70 |
+-----------+
1 row in set (0.00 sec)
mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| Europe/Oslo | Europe/Oslo |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> select '2013-03-31 03:10:00' - interval 1 hour;
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 1 hour |
+-----------------------------------------+
| 2013-03-31 02:10:00 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> -- Expected 2013-03-31 01:10:00.
mysql> select '2013-03-31 03:10:00' - interval 2 hour;
+-----------------------------------------+
| '2013-03-31 03:10:00' - interval 2 hour |
+-----------------------------------------+
| 2013-03-31 01:10:00 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> -- Expected 2013-03-31 00:10:00.
mysql> use test;
Database changed
mysql> drop table if exists `timezonetest`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `timezonetest` (
-> `dato` timestamp DEFAULT 0 NOT NULL,
-> PRIMARY KEY (`dato`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (UNIX_TIMESTAMP(dato))
-> (PARTITION p2012 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')) ENGINE = InnoDB,
-> PARTITION p2013 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')) ENGINE = InnoDB)
-> ;
Query OK, 0 rows affected, 4 warnings (0.01 sec)
mysql>
mysql> insert into timezonetest (dato) values
-> ('2012-03-31 00:00:00'),('2012-03-31 00:30:00'),('2012-03-31 01:00:00'),('2012-03-31 01:30:00'),('2012-03-31 03:00:00'),('2012-03-31 03:30:00'),
-> ('2013-03-31 00:00:00'),('2013-03-31 00:30:00'),('2013-03-31 01:00:00'),('2013-03-31 01:30:00'),('2013-03-31 03:00:00'),('2013-03-31 03:30:00');
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00';
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | timezonetest | p2012,p2013 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 1 hour;
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | timezonetest | p2012,p2013 | index | PRIMARY | PRIMARY | 4 | NULL | 12 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 |
+---------+------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 2 hour;
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | timezonetest | p2012,p2013 | range | PRIMARY | PRIMARY | 4 | NULL | 5 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Description: When using interval it seems that mysql is returning an incorrect date/time. When combine with a partition table this block the partition pruning algorithm. How to repeat: mysql> select '2013-03-31 03:10:00' - interval 1 hour; +-----------------------------------------+ | '2013-03-31 03:10:00' - interval 1 hour | +-----------------------------------------+ | 2013-03-31 02:10:00 | +-----------------------------------------+ 1 row in set (0.00 sec) This should have return 2013-03-31 01:10:00. mysql> select '2013-03-31 03:10:00' - interval 2 hour; +-----------------------------------------+ | '2013-03-31 03:10:00' - interval 2 hour | +-----------------------------------------+ | 2013-03-31 01:10:00 | +-----------------------------------------+ 1 row in set (0.00 sec) This should have return 2013-03-31 00:10:00. CREATE TABLE `timezonetest` ( `dato` timestamp DEFAULT 0 NOT NULL, PRIMARY KEY (`dato`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (UNIX_TIMESTAMP(dato)) (PARTITION p2012 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')) ENGINE = InnoDB, PARTITION p2013 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')) ENGINE = InnoDB) ; insert into timezonetest (dato) values ('2012-03-31 00:00:00'),('2012-03-31 00:30:00'),('2012-03-31 01:00:00'),('2012-03-31 01:30:00'),('2012-03-31 03:00:00'),('2012-03-31 03:30:00'), ('2013-03-31 00:00:00'),('2013-03-31 00:30:00'),('2013-03-31 01:00:00'),('2013-03-31 01:30:00'),('2013-03-31 03:00:00'),('2013-03-31 03:30:00'); mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00'; +----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | timezonetest | p2013 | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using index | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 1 hour; +----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | timezonetest | p2012,p2013 | index | PRIMARY | PRIMARY | 4 | NULL | 12 | Using where; Using index | +----+-------------+--------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 | | Warning | 1292 | Incorrect datetime value: '2013-03-31 02:10:00' for column 'dato' at row 1 | +---------+------+----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain partitions select * from timezonetest where dato >= '2013-03-31 03:10:00' - interval 2 hour; +----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | timezonetest | p2013 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where; Using index | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) Suggested fix: The interval function should return valid date based on the timezone. mysql> select '2013-03-31 03:10:00' - interval 1 hour; +-----------------------------------------+ | '2013-03-31 03:10:00' - interval 1 hour | +-----------------------------------------+ | 2013-03-31 02:10:00 | +-----------------------------------------+ 1 row in set (0.00 sec) Should return 2013-03-31 01:10:00. mysql> select '2013-03-31 03:10:00' - interval 2 hour; +-----------------------------------------+ | '2013-03-31 03:10:00' - interval 2 hour | +-----------------------------------------+ | 2013-03-31 01:10:00 | +-----------------------------------------+ 1 row in set (0.00 sec) Should return 2013-03-31 00:10:00.