MySQL: records are inserted by the clock, in the last 24 hours

Question

I am trying to list the number of records per hour inserted into the database in the last 24 hours. Each line displays the records inserted at that hour, as well as how many hours ago it was.

Here is my request now:

SELECT COUNT(*), FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
FROM `records`
WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(time)
ORDER BY time ASC

right now it is returning:

28  23
62  23
14  20
1    4
28  3
19  1

This shows two lines from 23 hours ago, when it should only show one hour. I think this has something to do with using NOW () instead of getting the time at the start of the hour, and I'm not sure how to do it.

There should be an easier way to do this.

Answer 1

HOUR(time), HOUR(time) , time. :

SELECT HOUR(time), COUNT(*)
FROM `records`
WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(time)
ORDER BY HOUR(time)

, :

SELECT COUNT(*), FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
FROM `records`
WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
ORDER BY FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )

, , NOW() . :

, , . , ​​, NOW() , .