Is there a flexible way to specify ranges of dates?

I am working with MySQL.

I have some queries that begin like this:

WITH dates (start_date, end_date) AS (
  SELECT '2020-11-01', '2020-11-02'
    UNION ALL SELECT '2020-11-02', '2020-11-03',
    UNION ALL SELECT '2020-11-03', '2020-11-04')
SELECT dates.start_date, dates.end_date, id, COUNT(*)
 FROM dates 
   INNER JOIN ...

I also sometimes need to run the same query, but with each date range being a week (Monday to Sunday), or a calendar month. Moreover, sometimes there are 100 or more of these, which is quite prone to typos in addition to occupying a lot of lines and taking a long time to write.

Is there more elegant and flexible way to achieve this ? Ideally I would like to be able to just specify a overall start date, an overall end date and a period (daily, weekly, monthly, yearly etc)

Answer

You can use a recursive CTE:

with recursive dates as (
      select date('2020-11-01') as start_date, date('2020-11-02') as end_date
      union all
      select start_date + interval 1 day, end_date + interval 1 day
      from dates
      where start_date < '2020-12-01'
     )
select *
from dates;

Here is a db<>fiddle. Of course, the logic would be a little different for months.