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

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 开发者_Go百科and 02:00:01.

How can this be done?

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)

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), ...

Just use DATE_FORMAT:

GROUP BY DATE_FORMAT(`date`, '%H:%i')

Since MySql stores dates/times as a full number, dividing the number by 100 will give you the minute:

GROUP BY FLOOR(`data` / 100)

Grouping by a number is more efficient then grouping by text.

If you want to group by minute regardless of the date:

GROUP BY FLOOR(`data` / 100) % 10000

For those who have searched the same result but using UNIX time (number of seconds from 1970-01-01) you can use:

SELECT
    FROM_UNIXTIME(`unix_time`, '%Y-%m-%d %H:%i') as minute,
    COUNT(id) as number_total
FROM my_table
GROUP BY minute

For more date functions > https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

If you want the dates to stay the same and just group by discarding the seconds, and your date field is date_time format (not VARCHAR). (Note: leave out -%i if you want to group by hour )

SELECT
   date_format(date,'%Y-%m-%d %H:%i:00') as minute_format
FROM your_table_name
GROUP BY 1;