Get a list of dates between two dates

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.

For MSSQL you can use this. It is VERY quick.

You can wrap this up in a table valued function or stored proc and parse in the start and end dates as variables.

DECLARE @startDate DATETIMEDECLARE @endDate DATETIMESET @startDate = '2011-01-01'SET @endDate = '2011-01-31';WITH dates(Date) AS (    SELECT @startdate as Date    UNION ALL    SELECT DATEADD(d,1,[Date])    FROM dates     WHERE DATE < @enddate)SELECT DateFROM datesOPTION (MAXRECURSION 0)GO

Edit 2021/01 (Dr. V):I liked this solution and made it work for mySQL V8. Here is the code, wrapping it into a procedure:

DELIMITER //CREATE PROCEDURE dates_between (IN from_date DATETIME,                               IN to_date DATETIME) BEGIN    WITH RECURSIVE dates(Date) AS    (        SELECT from_date as Date        UNION ALL        SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date    )    SELECT DATE(Date) FROM dates;END//DELIMITER ;

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_tableWHERE your_table.other_column IS NOT NULLHAVING 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.