CODEWITHSUNDEEP
mysqlsql
foglerit
foglerit

Reputation: 8279

MySQL: Expand date range into new rows

I have a table in MySQL that contains min and max date values for each key:

key |   minDate   |   maxDate
 1     2011-01-01   2011-01-10 
 2     2011-02-13   2011-02-15
 3     2011-10-19   2011-12-10

How can I create a new table that contains one row for each date between minDate and maxDate for each of the keys:

key |     Date   
 1     2011-01-01
 1     2011-01-02
 ...     ...
 1     2011-01-10
 2     2011-02-13
 2     2011-02-14
 2     2011-02-15
 3     2011-10-19
 ...     ...

Upvotes: 4

Views: 4035

Answers (4)

Mohith Bodhankar
Mohith Bodhankar

Reputation: 21

-- Below are 2 variables to set start date and end date 
set @start_date = '2020-01-01';
set @end_date = '2022-12-31';

-- Below is the recursive CTE which returns all the dates between Start date and End Date
WITH RECURSIVE ListDates(AllDates) AS
(   
    SELECT @start_date AS DATE
    UNION ALL
    SELECT DATE_ADD(AllDates, INTERVAL 1 DAY) 
    FROM ListDates 
    WHERE AllDates < @end_date
)
SELECT AllDates FROM ListDates

Upvotes: 1

ysth
ysth

Reputation: 98508

Using a recursive common table expression (requires mysql 8 or mariadb 10.2+):

with recursive expanded_ranges as (
    select id, mindate dt
    from ranges
    union all
    select expanded_ranges.id, expanded_ranges.dt+interval 1 day
    from expanded_ranges
    join ranges on expanded_ranges.id=ranges.id and dt<maxdate
)
select * From expanded_ranges;

fiddle

Upvotes: 1

pilcrow
pilcrow

Reputation: 58741

Using an integers table, you can do this:

    SELECT "key", minDate + INTERVAL i DAY
      FROM mytable
INNER JOIN integers
           ON i <= DATEDIFF(maxDate, minDate)

That assumes the "integers" table has its column named "i", of course.

From there you can populate your new table with INSERT INTO ... SELECT.

Upvotes: 6

aF.
aF.

Reputation: 66757

From memory, it could be something like this:

create table #res (
key int,
Date datetime
)

declare @minD datetime, @maxD datetime
select @minD = min(minDate), @maxD = max(maxDate) from tablename

while @minD <= @maxD do
   insert into #res
   select key, @minD from tablename where @minD >= minDate and @minD <= maxDate

   select @minD = dateadd(@minD, interval 1 day)
end while;

select key, Date from #res

drop table #res

Upvotes: -1

Related Questions