Daylight Saving Time problem

baoxiao · December 30, 2020, 4:22pm

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

baoxiao · December 30, 2020, 4:32pm

Very strange if I change the first query to below it return all data

select record_time,record_id from record where record_time>2015-11-01 00:59:59’ and record_time<‘2015-11-01 02:00:00’ limit 200;

baoxiao · December 30, 2020, 4:32pm

Does the Percona guy check this forum?

dendress · December 30, 2020, 4:32pm

Hi baoxiao,

Can you give us a little insight into your system? What version mysql are you running exactly? What operating system and version? What does select now() from the mysql command return versus date from the shell?

This will certainly help us in determining if you have trapped a bug or not.

Thanks,

Dov Endress
Percona Support Engineer

eroomydna · December 30, 2020, 4:32pm

An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. Regardless of your system time, TIMESTAMPS are always stored in UTC. This could be very important if your application handles time zones. Assuming that your datatype is TIMESTAMP you’re likely finding that the behaviour of storing the values back to UTC (behind the scenes) means that the time you’re looking for never existed (note that doesn’t imply dataloss, just that your data has the universal time attached to it of semantic reasons).

I spent some time reading into this much more to understand it and the best resources are the MySQL docs and to test it locally to get your head around it. Further more there are some additional TZ helper functionality in system tables: http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html

Hope this helps.

A

baoxiao · December 30, 2020, 4:32pm

The MySQL version is 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698
OS is: Red Hat Enterprise Linux Server release 6.6 (Santiago)
select now() return the same time as the date command from Linux shell

Thanks