I'm trying to list the number of records per hour inserted into a database for the last 24 hours. Each row displays the records inserted that hour, as well as how many hours ago it was.

Here's my query 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 returns:

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

That shows two rows from 23 hours ago, when it should only show one per hour. I think it has something to do with using NOW() instead of getting the time at the start of the hour, which I'm unsure on how to get.

There must be a simpler way of doing this.