Mysql how to fill missing dates in range?

sql

left-join

date-series

mysql-8

byAnton Shumikhin·Mar 1, 2025

Fill missing dates with a recursive CTE in MySQL 8.0+. Just grab your cup☕ and try this query:

WITH RECURSIVE DateSeries AS (
  -- Replace '2023-01-01' with your start date, even if it's your cat's birthday 🐱.
  SELECT '2023-01-01' AS missing_date 
  UNION ALL
  SELECT missing_date + INTERVAL 1 DAY FROM DateSeries WHERE missing_date < '2023-01-31' 
  -- Replace '2023-01-31' with your end date, yes even if it's pizza day! 🍕
)
SELECT * FROM DateSeries;

Plug in your start and end dates, and voilà! A complete date series without a calendar table. Efficient, right?

How to use the generated dates?

Once you've got your date series ready, you probably want to merge your existing data with it. We'll do a LEFT JOIN like this:

SELECT DateSeries.missing_date, COALESCE(your_table.score, 0) AS score
FROM DateSeries
LEFT JOIN your_table ON DateSeries.missing_date = your_table.date;

Now, every date gets a score. Dates without data? They get a participation trophy of 0.

But what about MySQL below 8.0?

Okay, what if you can't use MySQL 8.0 or its fancy CTEs? Well, we'll use the reliable old numbers table idea. Here's the code:

SELECT DATE_ADD('2023-01-01', INTERVAL seq_num DAY) AS date_series
FROM (
  SELECT seq_a.i+seq_b.i*10+seq_c.i*100 AS seq_num
  FROM 
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) seq_a,
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) seq_b,
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) seq_c
) seq
WHERE seq_num <= DATEDIFF('2023-01-31', '2023-01-01') 
ORDER BY seq_num;

Yes, it looks like a Chris Nolan plot, but bear with me. Use this to generate a series of dates then wrap it in a LEFT JOIN and add some COALESCE for good measure.

What if I need to maintain last 30 days' data?

Now, you may find yourself needing to keep a window of the last 30 days. Kit yourself out with this snazzy MySQL event:

CREATE EVENT purge_old_entries
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
  DELETE FROM your_table WHERE date < CURRENT_DATE - INTERVAL 30 DAY;

Don't forget to switch ON the event scheduler, or it’ll be lazier than a sloth on Sunday:

SET GLOBAL event_scheduler = ON;

Gives new meaning to "spring cleaning," right?

What about other ranges?

Maybe you need ranges other than dates, like 15-minute slots? We got you:

WITH RECURSIVE timeslot_series AS (
  SELECT CAST('2023-01-01 00:00:00' AS DATETIME) AS timeslot
  UNION ALL
  SELECT timeslot + INTERVAL 15 MINUTE FROM timeslot_series WHERE timeslot < '2023-01-01 23:45:00'
)
SELECT * FROM timeslot_series;

This generates 15-minute time slots within a single day. Replace 15 MINUTE with your desired interval.

Making your data presentation-worthy

Sorting your final output by date makes visualisation much easier:

SELECT missing_date, COALESCE(score, 0) AS score
FROM DateSeries 
LEFT JOIN your_table ON DateSeries.missing_date = your_table.`date` 
ORDER BY DateSeries.missing_date ASC;