When Daylight Saving Time happened on Nov 1 morning, the database seems can’t return the correct data based on time range from ‘2015-11-01 01:00:00’ to ‘2015-11-01 02:00:00’
select record_time,record_id from record where record_time>=‘2015-11-01 01:00:00’ and record_time<‘2015-11-01 02:00:00’ limit 200;
±--------------------±------ -----+
| record_time | record_id |
±--------------------±-----------+
| 2015-11-01 01:01:33 | 8045482165 |
| 2015-11-01 01:02:36 | 8044823645 |
| 2015-11-01 01:09:37 | 8045197592 |
| 2015-11-01 01:11:54 | 8045010818 |
| 2015-11-01 01:13:06 | 8045770685 |
| 2015-11-01 01:13:09 | 8045010833 |
| 2015-11-01 01:21:14 | 8045104830 |
| 2015-11-01 01:23:11 | 8046429336 |
| 2015-11-01 01:42:49 | 8045577290 |
±--------------------±-----------+
9 rows in set (0.00 sec)
select record_time,record_id from record where record_id>=8044823645 limit 20;
±--------------------±-----------+
| record_time | record_id |
±--------------------±-----------+
| 2015-11-01 01:02:36 | 8044823645 |
| 2015-11-01 01:10:01 | 8044823646 |
| 2015-11-01 01:10:01 | 8044823647 |
| 2015-11-01 01:10:01 | 8044823648 |
| 2015-11-01 01:10:01 | 8044823649 |
| 2015-11-01 01:10:01 | 8044823650 |
| 2015-11-01 01:10:01 | 8044823651 |
| 2015-11-01 01:10:01 | 8044823652 |
| 2015-11-01 01:10:01 | 8044823653 |
| 2015-11-01 01:10:01 | 8044823654 |
| 2015-11-01 01:10:01 | 8044823655 |
| 2015-11-01 01:10:01 | 8044823656 |
| 2015-11-01 05:10:01 | 8044823657 |
| 2015-11-01 01:10:01 | 8044823658 |
| 2015-11-01 01:10:01 | 8044823659 |
| 2015-11-01 01:10:01 | 8044823660 |
| 2015-11-01 01:10:01 | 8044823661 |
| 2015-11-01 05:10:01 | 8044823662 |
| 2015-11-01 01:10:01 | 8044823663 |
| 2015-11-01 01:10:01 | 8044823664 |
±--------------------±-----------+
The first query only return 9 rows, but the second query return more than 9 rows for the time from ‘2015-11-01 01:00:00’ to ‘2015-11-01 02:00:00’, the difference is first query use index on record_time, the second query use primary key on record_id,record_time, Is this a bug?
Thanks,
James