creating a time dataset by using mySql while loop

user_18174380 0
Viewed 28

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 -

Date
29/05/2022
28/05/2022
27/05/2022
...
29/04/2022

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!

mysql loops
1 Answers

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.

Bill_Karwin_208600
Related