How to Avoid Gaps in Data in MySQL

If you're grouping by time and you don't want gaps in your report data, you need to generate a series of time values and use it to do an outer join with your data. Prior to MySQL 8, you can do this using variables. In the following example we extracted the number of rentals per hour from the Sakila Sample Database:

-- The first line is to make the first value of statement below 0:00:00, not 1:00:00
-- LIMIT 720 gives 30 days worth of hourly values
SET @n:=('2005-05-25' - INTERVAL 1 HOUR);
SELECT
  hours.this_hour,
  count(rental.rental_id)
FROM
  (SELECT (SELECT @n:= @n + INTERVAL 1 HOUR) this_hour
   FROM inventory LIMIT 720) hours
LEFT JOIN rental ON (hours.this_hour=date_format(rental.rental_date,'%Y-%m-%d %H:00:00'))
GROUP BY hours.this_hour;

There are serious limitations with the above method. For one thing, you may ask what does the inventory table have to do with rentals? It does not have anything to do with it directly. The inventory table got chosen because it has a few thousand rows and we needed one that has at least 720 to generate our series of time values. In reality any table with a large number of rows can be used for this. But what if there is none available?

Fortunately, starting with MySQL 8, you can instead use a common table expression:

WITH RECURSIVE my_hours AS
(
  SELECT 0 as inc
  UNION ALL
  SELECT 1+inc
  FROM my_hours WHERE inc<=720
)
SELECT
  hours.this_hour,
  count(rental.rental_id)
FROM
  (SELECT '2005-05-25' + interval inc hour as this_hour
   FROM my_hours) as hours
LEFT JOIN rental ON (hours.this_hour=date_format(rental.rental_date,'%Y-%m-%d %H:00:00'))
GROUP BY hours.this_hour;