CODEWITHSUNDEEP
mysqlsql
user2913080
user2913080

Reputation: 13

MYSQL: How to group the count of rows for each given time period?

i've tried to understand other similar problems but I am kind of lost. :(

So essentially I have many transaction listings that occur all through the day and each have a timestamp

TRANSACTION TIME
-----------------

2013-10-01 01:02:03         ...
2013-10-01 08:02:03         ...
2013-10-01 11:02:03         ...
2013-10-01 20:02:03         ...

I just want a table that will tell me how many transactions happened for each given hour. So...

TIME PERIOD                                               COUNT
-------------------------------------------
2013-10-01 00:00:00 -> 2013-10-01 01:00:00                 23

etc.

Any help will be greatly appreciated :)

Upvotes: 1

Views: 732

Answers (1)

mpen
mpen

Reputation: 282805

Just format your date to drop the minutes and seconds. When you group it, it will have the same value, so they will group together.

SELECT DATE_FORMAT(yourdate, '%Y-%m-%d %H:00:00') hr, COUNT(*) GROUP BY hr

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Upvotes: 4

Related Questions