clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335738 fiddles created (27463 in the last week).

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`;
Date
2021-07-02
2021-07-03
2021-07-04
2021-07-06
2021-07-09
2021-07-11
2021-07-14
2021-07-17
2021-07-18
2021-07-21
2021-07-22
2021-07-23
2021-07-24
2021-07-25
2021-07-26
2021-07-27
2021-07-28
 hidden batch(es)


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
range_start range_finish range_length
2021-07-02 2021-07-04 3
2021-07-17 2021-07-18 2
2021-07-21 2021-07-28 8
 hidden batch(es)