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’
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?
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.
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: [url]http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html[/url]
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