Get mySQL rows in minute-by-minute time range over multiple hours

Question

I could try doing this by PHP but I think it could be done simply in mySQL. I have rows in mySQL with a date time over multiple hours. I want to return the counts for each minute interval during those multiple hours.

GROUP BY MINUTE(date)

gives me 60 rows, but it doesn't give me the counts for 01:00:00 - 01:01:00 differently from 02:00:00 and 02:00:01.

How can this be done?

Answer 1

MySQL minute function is literally taking the minute number and grouping by that. Try grouping by hour then minute:

GROUP BY HOUR(date), MINUTE(date) 

Answer 2

New answer for old question!

To group by minute, you can simply:

SELECT (unix_timestamp(`date`) - unix_timestamp(`date`)%60) groupTime, count(*)  FROM yourTable # WHERE clause GROUP BY groupTime 

With this solution, you will never mixed datetimes of a minute with datetimes of the same minute of another hour, day, ...

Plus, It's an evolutive solution because, by changing "60" to another number, you can group by a couple of minutes (120), by a day (86400), ...