Agrupar loggings hora por hora o día por día y llenar vacíos con cero o nulo en mysql

He escrito una consulta que count los loggings hora por hora:

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'); 

el resultado es:

 2012-02-22 13 2280 2012-02-22 15 1250 2012-02-22 16 1245 2012-02-22 19 1258 

Pero necesito un resultado como este:

 2012-02-22 13 2280 2012-02-22 14 0 2012-02-22 15 1250 2012-02-22 16 1245 2012-02-22 17 0 2012-02-22 18 0 2012-02-22 19 1258 

¡También tengo estas consultas que se agrupan por día y mes también!

 select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*) from req group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD'); select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*) from req group by TO_CHAR(copied_timestamp, 'YYYY-MM'); 

Necesito que sus vacíos se llenen con cero o nulo también. Cualquier ayuda es muy apreciada.

Nota: Hay una respuesta para esta pregunta en Oracle usando CONNECT BY pero necesito la respuesta en Mysql porque Mysql no admite CONNECT BY . Aqui esta el link

Genere una tabla con una sola columna de dates y horas que contenga todas las dates y horas dentro de un range razonable (por ejemplo, de 1900 a 2200). Luego, haga una LEFT JOIN desde esta tabla hasta su consulta actual.

Para que esta técnica funcione correctamente, es probable que deba agregar una columna indexada a su tabla que contenga una timestamp convertida (su date de copied_timestamp convertida a DATETIME , networkingondeada a la hora)

 SELECT date_hour, count(req.converted_timestamp) FROM dates_hours LEFT JOIN req ON req.converted_timestamp = dates_hours.date_hour WHERE date_hour BETWEEN (SELECT MIN(req.converted_timestamp) FROM req) AND (SELECT MAX(req.converted_timestamp) FROM req) GROUP BY date_hour 

Para generar la tabla dates_hours :

 CREATE TABLE dates_hours (date_hour DATETIME PRIMARY KEY); DELIMITER $$$ CREATE PROCEDURE generate_dates_hours (to_date DATETIME) BEGIN DECLARE start_date DATETIME; DECLARE inc INT; SELECT MAX(date_hour) INTO start_date FROM dates_hours; IF start_date IS NULL THEN SET start_date = '1900-01-01'; END IF; SET inc = 1; WHILE start_date + INTERVAL inc HOUR <= to_date DO INSERT INTO dates_hours VALUE (start_date + INTERVAL inc HOUR); SET inc = inc +1; END WHILE; END $$$ DELIMITER ; CALL generate_dates_hours('2200-01-01'); 

De acuerdo, ahora que estoy revisando a mí mismo, me doy count de que esta es una solución bastante descabellada. Espero que a alguien se le ocurra uno más elegante.

Creé una tabla llamada TBL_NUMBERS

 CREATE TABLE `TBL_NUMBER` (`n` int(11) NOT NULL) 

y loggings insertados del 1 al 1000. Ahora puedo generar cualquier tipo de range de date usando esta consulta:

 SELECT '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] or as dateRange FROM TBL_NUMBER WHERE '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] <= '2012-06-23'; 

Entonces puedo unirme a esta tabla con mis resultados para llenar el intervalo de dates. Si necesito más de 1000 dates, puedo insert más loggings en TBL_NUMBER

Si tienes una idea mejor, estoy ansioso por saber eso;)