I'm trying to create a time dataset populated with 30 dates by using a WHILE loop. I'm doing it because I don't have the ability to use CTE in mySql 5.7. afterwards I'll emnbedd this dataset in a subquery. So, let's say the time dataset should look like this -

I tried to right a WHILE loop as writen below but failed to do so.. If, somebody have an idea, it's highly appreciated.

    set @i = DATE_ADD(CURRENT_DATE(), DAY -2 INTERVAL)
    set @timeset = select CURRENT_DATE()
     
    while @i < CURRENT_DATE() DO
    timeset = @timeset
    union
    select DATE_ADD(@timeset , DAY 1 INTERVAL)
    
    set @i = DATE_ADD(@i, DAY 1 INTERVAL)
    end WHILE 

thanks!

CodePudding user response：

If you do a SELECT inside the loop, each row will be its own result set. Probably not what you want.

If you need to generate it on the fly, I'd choose to do it as one query without a loop, for example something like this:

select current_date()   interval d10.n d1.n day
from (
 select 0 as n 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
) as d1
cross join (
 select 0 as n union select 10 union select 20
) as d10;

This is admittedly pretty awkward. That's why the comment above suggested to create a "calendar table" that is prepopulated with one row per day, and then you can query that table for a range of rows. Even if you populate it with a row for every day for the next 100 years, that's only about 36525 rows, right? Still not a large table by MySQL standards.

Or you could upgrade to MySQL 8.0, which you should do by October 2023 anyway, because that's when MySQL 5.7 reaches its end of life.