# Daylight Saving Time problem

**URL:** <https://forums.percona.com/t/daylight-saving-time-problem/4488>\
**Category:** Other MySQL® Questions\
**Created:** [November 2, 2015, 11:09am UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488 "2015-11-02T11:09:27Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![baoxiao](https://avatars.discourse-cdn.com/v4/letter/b/f6c823/32.png) [@baoxiao](https://forums.percona.com/u/baoxiao)\
**Post date:** [November 2, 2015, 11:09am UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488/1 "2015-11-02T11:09:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![baoxiao](https://avatars.discourse-cdn.com/v4/letter/b/f6c823/32.png) [@baoxiao](https://forums.percona.com/u/baoxiao)\
**Post date:** [November 4, 2015, 10:52am UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488/2 "2015-11-04T10:52:28Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![baoxiao](https://avatars.discourse-cdn.com/v4/letter/b/f6c823/32.png) [@baoxiao](https://forums.percona.com/u/baoxiao)\
**Post date:** [November 6, 2015, 9:53am UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488/3 "2015-11-06T09:53:47Z")

</div>

Does the Percona guy check this forum?

---

<div class="post-metadata">

**Author:** ![dendress](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dendress/32/1091_2.png) [@dendress](https://forums.percona.com/u/dendress)\
**Post date:** [November 9, 2015, 5:10pm UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488/4 "2015-11-09T17:10:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![eroomydna](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/eroomydna/32/941_2.png) [@eroomydna](https://forums.percona.com/u/eroomydna)\
**Post date:** [November 10, 2015, 3:14am UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488/5 "2015-11-10T03:14:51Z")

</div>

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]](http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html%5B/url%5D)

Hope this helps.

A

---

<div class="post-metadata">

**Author:** ![baoxiao](https://avatars.discourse-cdn.com/v4/letter/b/f6c823/32.png) [@baoxiao](https://forums.percona.com/u/baoxiao)\
**Post date:** [November 13, 2015, 2:27pm UTC](https://forums.percona.com/t/daylight-saving-time-problem/4488/6 "2015-11-13T14:27:12Z")

</div>

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;43171](#):
>
> 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