CODEWITHSUNDEEP
mysqltimegroup-by
Ian
Ian

Reputation: 12241

Retrieve rows grouped by hour with MySQL

I have a table containing access logs. I want to know how many accesses to resource_id '123' occured in each hour in a 24 hour day.

My first thought for retrieving this info is just looping through each hour and querying the table in each loop with something like... and time like '$hour:%', given that the time field holds data in the format 15:47:55.

Is there a way I can group by the hours and retrieve each hour and the number of rows within each hour in a single query?

Database is MySQL, language is PHP.

Upvotes: 12

Views: 14197

Answers (2)

user156676
user156676

Reputation:

You can use the function HOUR to get the hour out of the time. Then you should be able to group by that.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562330

SELECT HOUR(MyDatetimeColumn) AS h, COUNT(*)
FROM MyTable
GROUP BY h;

Upvotes: 37

Related Questions