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?

What about the date itself? do you want to group Sundays minutes together with Mondays minutes? — The Scrum Meister, Feb 17 '11 at 03:04

score 40 · Accepted Answer · answered Feb 17 '11 at 02:56

40

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)

answered Feb 17 '11 at 02:56

beer_monk

946
7
7

score 25 · Answer 2 · answered Dec 19 '14 at 13:57

25

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

answered Dec 19 '14 at 13:57

didier2l

305
3
4

1

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! – John Hunt May 18 '17 at 10:20
1

really really awesome! – bluepinto Mar 22 '18 at 05:18
Best answer for me... – Feshibaba May 22 '23 at 22:37

score 20 · Answer 3 · answered Feb 17 '11 at 02:57

20

Just use DATE_FORMAT:

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

answered Feb 17 '11 at 02:57

ircmaxell

163,128
34
264
314

1

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`` – Ryan Jan 26 '19 at 01:09

score 8 · Answer 4 · answered Feb 17 '11 at 03:00

8

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

answered Feb 17 '11 at 03:00

The Scrum Meister

29,681
8
66
64

1

very halpfull answer Thanks !:) 1000 10 minute 10000 1 hr 20000 2 hr – Smoke Nov 21 '14 at 06:53

score 2 · Answer 5 · answered Feb 27 '19 at 09:25

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

score 1 · Answer 6 · answered Aug 21 '22 at 16:38

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;

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

6 Answers6