Group by hour of day then Count # in each group

Question

I am having difficulity with my current task. It deals with displaying the most popular time of day for room rental.

I have a large database (250,000+ entries), with entry dates/times stored as datetime. Example:

2015-06-10 19:30:15
2015-06-10 18:30:15
2015-06-10 14:03:15
2015-06-09 16:30:15
2015-06-09 14:30:15
2015-06-09 14:50:15
2015-06-08 13:30:15
2015-06-08 14:05:15

What I need to do, is group by 1 hour intervals, then return the count of number of entries in each interval.

Example (Does not reflect data in first example set):

09:01 - 10:00 = 2
10:01 - 11:00 = 3
10:01 - 12:00 = 6
12:01 - 13:00 = 8
13:01 - 14:00 = 5

The only way I have been able to do this, is create a sql query for each interval, but I am very dissatisfied doing it in that manor.

Can anyone help me understand how to better accomplish this type of query?

P.S. I am accessing MySQL via PHP if that is relevant to this answer.

possible duplicate of [DateTime group by date and hour](http://stackoverflow.com/questions/14845981/datetime-group-by-date-and-hour) — Jay Blanchard, Jun 10 '15 at 18:29
I did search, never saw (or didn't register) that link you shared. Thank you! — Jason, Jun 10 '15 at 18:39

score 1 · Accepted Answer · answered Jun 10 '15 at 20:43

1

Select date_format(yourdatetime, '%H'), count(*)
From yourtable
Group by date_format(yourdatetime, '%H');

answered Jun 10 '15 at 20:43

symcbean

47,736
6
59
94

Group by hour of day then Count # in each group

1 Answers1

Linked