MySQL: Find Extra Days From List Of Date Ranges

I have a following rows

start        end
2015-02-12   2015-02-16
2015-02-17   2015-02-18
2015-02-20   2015-02-20

Now I want to calculate number of days for a date range which are not in these ranges. So for example I have date range like this

2015-02-11 - 2015-02-19

so the result for this input should be 2 days. How? for a clear explanation

start        end 
2015-02-12   2015-02-16   // 1 day which is 2015-02-11 
2015-02-17   2015-02-18   // 1 day which is 2015-02-19 range
2015-02-20   2015-02-20  // 0 day because this row starting date is greater than input limit.

I want MySQL query for this.

You could use a Dates table to help with this.

This table will hold a single row-per-day from 2015-01-01 into the future:

CREATE TABLE Days
(
  Day date NOT NULL
);

INSERT INTO Days (Day)
SELECT ADDDATE('2015-01-01', INTERVAL (n1.Num + (n2.Num * 10) + (n3.Num * 100) + (n4.Num * 1000)) DAY)
FROM (
  SELECT 0 AS Num 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
) n1
CROSS JOIN (
  SELECT 0 AS Num 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
) n2
CROSS JOIN (
  SELECT 0 AS Num 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
) n3
CROSS JOIN (
  SELECT 0 AS Num 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
) n4;

This is the list of ranges you have defined in your question:

CREATE TABLE RangeList
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO RangeList (FromDate, ToDate)
VALUES ('2015-02-12', '2015-02-16')
  , ('2015-02-17', '2015-02-18')
  , ('2015-02-20', '2015-02-20');

This is the range of days you're interested in checking:

CREATE TABLE DaysNotInRange
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO DaysNotInRange (FromDate, ToDate)
VALUES ('2015-02-11', '2015-02-19');

This will show you the missing days:

SELECT Days.Day
FROM Days
    INNER JOIN DaysNotInRange ON Days.Day >= DaysNotInRange.FromDate 
        AND Days.Day = RangeList.FromDate 
        AND Days.Day <= RangeList.ToDate
  )
ORDER BY Day;

The results:

╔════════════╗
║ Day ║
╠════════════╣
║ 2015-02-11 ║
║ 2015-02-19 ║
╚════════════╝

StackExchange Database Administrators Q#199374, answer score: 3