I would use this stored procedure to generate the intervals you need into the temp table named time_intervals, then JOIN and aggregate your data table with the temp time_intervals table.

The procedure can generate intervals of all the different types you see specified in it:

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

Similar example data scenario at the bottom of this post, where I built a similar function for SQL Server.

You can use MySQL's user variables like this:

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num is -1 because you add to it the first time you use it. Also, you can't use "HAVING date_sequence" because that makes the user variable increment twice for each row.

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data. Since there were no entries for those dates, the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date.

We have a job that runs every month to ensure that the table is populated 5 years out into the future. The table is created thus:

create table all_dates (
    dt date primary key
);

No doubt there are magical tricky ways to do this with different DBMS' but we always opt for the simplest solution. The storage requirements for the table are minimal and it makes the queries so much simpler and portable. This sort of solution is almost always better from a performance point-of-view since it doesn't require per-row calculations on the data.

The other option (and we've used this before) is to ensure there's an entry in the table for every date. We swept the table periodically and added zero entries for dates and/or times that didn't exist. This may not be an option in your case, it depends on the data stored.

If you really think it's a hassle to keep the all_dates table populated, a stored procedure is the way to go which will return a dataset containing those dates. This will almost certainly be slower since you have to calculate the range every time it's called rather than just pulling pre-calculated data from a table.

But, to be honest, you could populate the table out for 1000 years without any serious data storage problems - 365,000 16-byte (for example) dates plus an index duplicating the date plus 20% overhead for safety, I'd roughly estimate at about 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), a minuscule table in the scheme of things.

Borrowing an idea from this answer, you can set up a table with 0 through 9 and use that to generate your list of dates.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

This will allow you to generate a list of up to 1000 dates. If you need to go larger, you can add another cross join to the inner query.

Elegant solution using new recursive (Common Table Expressions) functionality in MariaDB >= 10.3 and MySQL >= 8.0.

WITH RECURSIVE t as (
    select '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
select * FROM t;

The above returns a table of dates between '2019-01-01' and '2019-04-30'.

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

I've seen variations with table-valued functions, etc.

You can also keep a permanent list of dates. We have that in our data warehouse as well as a list of times of day.

You can use this

SELECT CAST(cal.date_list AS DATE) day_year
FROM (
  SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
  FROM (
        SELECT @xi:=@xi+1 as xc from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
        (SELECT @xi:=-1) xc0
    ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;

No temp table | No procedure | No functions

The example below present one month date range - from day 1 to the last day of the same month. You can change the date range by playing with @e `INTERVAL` values.

SET @s := CAST('2021-12-01' AS DATE); -- start at 2021-12-01
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- ends at 2021-12-31

WITH RECURSIVE d_range AS
(
       SELECT @s AS gdate -- generated dates
       UNION ALL
       SELECT gdate + INTERVAL 1 day
       FROM   d_range
       WHERE  gdate < @e
)
SELECT *
FROM   d_range;

Result:

| gdate       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)

Note. This query uses RECURSION there for it is quite slow 31 rows in set (0.037 sec), when selecting longer date ranges it will get even slower.

Create a stored procedure which takes two parameters a_begin and a_end. Create a temporary table within it called t, declare a variable d, assign a_begin to d, and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d. Finally SELECT * FROM t.

I've been fighting with this for quite a while. Since this is the first hit on Google when I searched for the solution, let me post where I've gotten so far.

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

Replace [yourTable] with a table from your database. The trick is that the number of rows in the table you select must be >= the number of dates you want to be returned. I tried using the table placeholder DUAL, but it would only return one single row.

No Function, No Procedures

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
    UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
    UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) T CROSS JOIN ( SELECT 0 U
    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
  ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

UNIONS define the length and range of available dates to elect from. So always make sure to make it log enough.

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s

Based on this article
Tested and working as expected

Improving on @brad answer, I used a CTE with a cursor:

DECLARE cursorPeriod CURSOR FOR SELECT * FROM (
   WITH RECURSIVE period as
        (
             SELECT '2019-01-01' as dt
             UNION
             SELECT DATE_ADD(period.dt, INTERVAL 1 DAY) FROM period WHERE DATE_ADD(period.dt, INTERVAL 1 DAY) <= '2019-04-30'
        )
   SELECT DATE(dt) FROM period ORDER BY dt ) AS derived_table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

Portable solution

-- Generate 
WITH 
tdates AS (
  WITH   t19 AS (
          SELECT  0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union 
          select 7     union select 8 union select 9 )    
   SELECT  adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datei 
   FROM  t19 AS t0, t19 AS t1, t19 AS t2, t19 AS t3,  t19 AS t4 
)

select * from tdates
where datei between '2012-02-10' and '2012-02-15'