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?

Solution 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)

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

Solution 3

Just use DATE_FORMAT:

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[The Scrum Meister]

What about the date itself? do you want to group Sundays minutes together with Mondays minutes?

[Smoke]

very halpfull answer Thanks !:) 1000 10 minute 10000 1 hr 20000 2 hr

[John Hunt]

One major side-benefit to this solution is that if you store timestamps rather than dates then you can remove the unix_timestamp() parts and still group by minutes for example - amazing!

[bluepinto]

really really awesome!

[Ryan]

Thanks. This was helpful for me: SELECT DATE_FORMAT(`execute_at`, '%Y-%m-%d %H:%i'), COUNT(*) FROM automations GROUP BY DATE_FORMAT(`execute_at`, '%Y-%m-%d %H:%i') ORDER BY count(*) DESC