MySQL Date add interval as a list

Question

Having a table with fields like this: (id, date, intervaldays) while as an example the date='2015-06-30' and the interval days =3 What is the query for list all the dates between (date - (date+intervaldays)) if there is any, so the output will be like:

id 2015-06-30
id 2015-07-01
id 2015-07-02

I have in mind that DATE_ADD(date, INTERVAL intervaldays DAY) will give the last date, so how can I list the days between

Eiad Samman Eiad Samman · Accepted Answer · 2015-06-30T21:14:55

Well actually thanks to @Mark Bannister in this post I came up with this query that works perfectly

select selected_date from 
mytable JOIN 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 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) t0,
 (select 0 t1 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) t1,
 (select 0 t2 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) t2,
 (select 0 t3 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) t3,
 (select 0 t4 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) t4
 ) v
 ON v.selected_date >= start_date AND v.selected_date<=DATE_ADD(start_date, INTERVAL days_interval DAY)

Any improvements?

MySQL Date add interval as a list

1 Answers