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;