MySQL: expanding date range to new lines
I have a table in MySQL that contains the minimum and maximum 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 to create a new table containing 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
... ...
+3
source to share
2 answers
Using a table of integers, you can do this:
SELECT "key", minDate + INTERVAL i DAY
FROM mytable
INNER JOIN integers
ON i <= DATEDIFF(maxDate, minDate)
This assumes the "integers" table has its own column named "i" of course.
From there, you can fill in your new spreadsheet INSERT INTO ... SELECT
.
+3
source to share
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
0
source to share