Not the answer you need?
Register and ask your own question!

Daylight Saving Time problem

baoxiaobaoxiao ContributorCurrent User Role Beginner
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

Comments

  • baoxiaobaoxiao Contributor Current User Role Beginner
    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;
  • baoxiaobaoxiao Contributor Current User Role Beginner
    Does the Percona guy check this forum?
  • dendressdendress Entrant Current User Role Beginner
    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
  • eroomydnaeroomydna MySQL Addict Current User Role Beginner
    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
  • baoxiaobaoxiao Contributor Current User Role Beginner
    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

    dendress wrote: »
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.