Row for each hour even if there is no record

Question

Possible Duplicate:
How do I make a row generator in mysql
generate_series equivalent in mysql

I've got a trouble with creating mysql Query.

My PHP script executes this query on each run: INSERT INTO Executes SET UserIp='%s' (%s is user IP)

Executes table is:

ExecuteId UNSIGNED BIGINT AI PRIMARY 
Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP INDEX 
UserIp CHAR(24) ... | Some Columns

I want to retrive number of Executes in each hour. The most obvious solution would be:

SELECT COUNT(*) as ExecutesNum, DATE(Date) as D, HOUR(Date) as H GROUP BY D, H

And it works, BUT it does not create rows for hours where there were no executes.

What should I modify to get result like:

  1 | 2012-09-01 | 14

**0 | 2012-09-01 | 15**

 11 | 2012-09-01 | 16

  1 | 2012-09-01 | 17

Answer 1

This is a rather common problem, which I usually solve by creating a temporary table containing all the hours, like this:

DROP TABLE IF EXISTS hours;
CREATE TABLE hours (hour VARCHAR(13) PRIMARY KEY);
DROP PROCEDURE IF EXISTS fill_hours;

DELIMITER |
CREATE PROCEDURE fill_hours(start_time DATETIME, end_time DATETIME)
BEGIN
  DECLARE crt_time DATETIME;
  SET crt_time=DATE_SUB(start_time, INTERVAL DATE_FORMAT(start_time, '%i:%s') MINUTE_SECOND);
  WHILE crt_time < end_time DO
    INSERT INTO hours VALUES(DATE_FORMAT(crt_time, '%Y-%m-%d-%H'));
    SET crt_time = DATE_ADD(crt_time, INTERVAL 1 HOUR);
  END WHILE;
END |

CALL fill_hours( (SELECT MIN(Date) FROM Executes), (SELECT MAX(Date) FROM Executes) );

You can then join this table to the original one to get what you want:

SELECT
h.hour,
COUNT(e.ExecuteId)
FROM hours h
LEFT JOIN Executes e ON DATE_FORMAT(e.Date, "%Y-%m-%d-%H") = h.hour
GROUP BY h.hour