MySQL Group By Hours

Modifying on @eggyal answer, as another good use case would be to display day as well along with hours.

Suppose you need COUNT of records of past 7 days which is of 24 hour each.

SELECT
   dayname(date_sub(curdate(), INTERVAL 1 DAY)) AS Day,
   CONCAT(Hour, ':00-', Hour+1, ':00') AS Hour,
   COUNT(created) AS `usage`
FROM
   history
   RIGHT JOIN
      (
         SELECT  0 AS Hour
         UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
         UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
         UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
      )
      AS totalhours
      ON HOUR(created) = HOUR
      AND DATE(created) = date_sub(curdate(), INTERVAL 1 DAY)
      OR created IS NULL
GROUP BY
   Hour
ORDER BY
   Hour;

Modifying this in the business logic of the backend code or in a stored procedure,

INTERVAL 1 DAY

can yield query results of past 7 days along with the name of the day.

Your existing query can be reduced to:

SELECT   CONCAT(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours
  ,      COUNT(*) AS `usage`
FROM     history
WHERE    created BETWEEN '2012-02-07' AND NOW()
GROUP BY HOUR(created)

To display every hour, including those for which there is no data, you need to outer join with a table containing all the hours for which you want data. You can build such a table in your query using UNION:

SELECT   CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours
  ,      COUNT(created) AS `usage`
FROM     history
  RIGHT JOIN (
                   SELECT  0 AS Hour
         UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
         UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
         UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS AllHours ON HOUR(created) = Hour
WHERE    created BETWEEN '2012-02-07' AND NOW() OR created IS NULL
GROUP BY Hour
ORDER BY Hour

However, the treatment of groups for which no data exists is really a matter for business logic that's best placed in your data access layer rather than in the database itself: indeed it should be trivial for your application to use a zero value whenever an hour is absent.

Given a table Log with columns ts and value, the following will give the hourly average for the last 24 hours (assuming each hour has at least one row).

SELECT 
    FROM_UNIXTIME( TRUNCATE( UNIX_TIMESTAMP( `ts` )/3600, 0 ) * 3600 ) as h,
    AVG( `value` ) as v,
    COUNT( * ) as q
FROM 
    Log
GROUP BY 
   h
ORDER BY 
   h desc
LIMIT
    24

The column ts can be a timestamp or datetime column while value is anything that AVG() will accept.