Prueba esta consulta y me dices:
Código sql:
Ver originalSELECT DATE( ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) ) AS DIA, COUNT( * ) AS TOTAL
FROM tabla
WHERE ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) > DATE_ADD( DATE_ADD( NOW( ) , INTERVAL 6 HOUR ) , INTERVAL -31
DAY )
GROUP BY DATE( ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) )
ORDER BY DATE( ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) ) DESC