CREATE TABLE test (`Date` DATE) SELECT '2021-07-28' `Date` UNION ALL SELECT '2021-07-27' UNION ALL SELECT '2021-07-26' UNION ALL SELECT '2021-07-25' UNION ALL SELECT '2021-07-24' UNION ALL SELECT '2021-07-23' UNION ALL SELECT '2021-07-22' UNION ALL SELECT '2021-07-21' UNION ALL SELECT '2021-07-18' UNION ALL SELECT '2021-07-17' UNION ALL SELECT '2021-07-14' UNION ALL SELECT '2021-07-11' UNION ALL SELECT '2021-07-09' UNION ALL SELECT '2021-07-06' UNION ALL SELECT '2021-07-04' UNION ALL SELECT '2021-07-03' UNION ALL SELECT '2021-07-02'; SELECT * FROM test ORDER BY `Date`;

SELECT t1.`Date` range_start, MIN(t2.`Date`) range_finish, DATEDIFF(MIN(t2.`Date`), t1.`Date`) + 1 range_length FROM test t1 JOIN test t2 ON t2.`Date` >= t1.`Date` WHERE NOT EXISTS ( SELECT NULL FROM test t3 WHERE t3.`Date` = t1.`Date` - INTERVAL 1 DAY ) AND NOT EXISTS ( SELECT NULL FROM test t4 WHERE t4.`Date` = t2.`Date` + INTERVAL 1 DAY ) GROUP BY range_start HAVING range_length > 1 ORDER BY range_start