Get every missing date between date range

Question

I have a table with a date field that varies from month to month, for example:

id    import_date   name ...
1     2017-02-01    Leonardo
7     2017-03-01    Alison
8     2017-06-01    Juliet
11    2017-09-01    Laura

Every first day in month we import some data to this table. As you can see, some register from January, April, May, July and August are missing, in this example.

How can I get only the missing dates between a start and end date range?

see https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql — giorgiga, Feb 06 '18 at 20:42
@giorgiga this only generate dates 1 by 1 from an initial date. I need to find the gaps between dates. — Leonardo, Feb 06 '18 at 20:54
generate all dates in the range and then filter out the ones that are present in the table — giorgiga, Feb 06 '18 at 20:57
@Barmar please reopen it so I can give the right answer to someone who might need it. — Leonardo, Feb 07 '18 at 17:01

score 0 · Answer 1 · answered Feb 09 '18 at 12:09

That's what I've done:

SELECT data_series.import_date FROM 
(
    SELECT DATE_ADD(:startDate, INTERVAL @i :=@i + 1 MONTH) AS import_date 
        FROM imports, (SELECT @i := -1) i, 
        (SELECT MIN(import_date) as minVal, MAX(import_date) as maxVal 
    FROM imports WHERE import_date BETWEEN :startDate AND :endDate) AS smallerSet 
    WHERE @i < DATEDIFF(smallerSet.maxVal, smallerSet.minVal)
) AS data_series 
LEFT JOIN 
(
    SELECT DISTINCT import_date FROM imports 
    WHERE reference_date BETWEEN :startDate AND :endDate
) AS import_period 
ON data_series.import_date = import_period.import_date 
WHERE import_period.import_date IS NULL

where :startDate and :endDate are variables I'm using in Spring Boot.

Get every missing date between date range

1 Answers1