snowflakes74 snowflakes74 - 1 month ago 11
MySQL Question

MySQL get records by hour interval

I need to show a timeline from MySQL table. Basically retrieve count of records by each hour. My TimeSigned column is DateStamp

Login

Id TimeSigned MarkedBy
1. 2016-03-14 05:12:17 James
2. 2016-03-14 05:30:10 Mark
3. 2016-03-14 06:10:00 James
4. 2016-03-14 07:30:10 Mary


I am using following query but it brings wrong results.

SELECT COUNT(Id) From Logins WHERE HOUR(TimeSigned) > 5 AND HOUR(TimeSigned) < 6


I was expecting it to return a count of 2 (i.e. 1 and the 2 record are within the 5-6 time range) but it brings back 0.

I have created a sqlfiddle here SQL Fiddle

Answer

Use = in your first condition. there is nothing between 5 and 6 so it will give count 0

SELECT COUNT(Id) From Logins WHERE HOUR(TimeSigned) >= 5 AND HOUR(TimeSigned) < 6