MySQL For loop? Is it possible?-CodePudding

I have a query in MySQL that works well. It runs over one year of data at a time.

2020-01-01 to 2021-01-01

The problem is, it needs to run in monthly increments. So it needs to run as:

2020-01-01 to 2021-01-01
2020-02-01 to 2021-02-01
2020-03-01 to 2021-03-01
2020-04-01 to 2021-04-01
etc...

The only way I can think about doing this at the minute is one month at a time. I set the FROM and TO timeperiods at the top of the script.

But to run it for 3 years, it needs to run 36 times, if I use this method.

Is there a way to loop through dates & run the query over and over again, so I dont need to do it manually?

SET @tp = '2019/01/01';
SET @tp2 = '2020/01/01';

Thanks

CodePudding user response：

You don't need a for loop if you just want to add one year to all timestamps. I don't know the data-structure, but somthing like :

UPDATE yourTable
SET yourDate = DATE_ADD(yourDate , INTERVAL 1 YEAR)

should be enough.

CodePudding user response：

You can use Recursive CTE to achieve the result without using loops or stored procedures. Just enter the correct start and end dates. This is the code:

WITH RECURSIVE cte1 AS 
(SELECT '2019-01-01' as firstdate, '2020-01-01' as seconddate
UNION ALL
SELECT (firstdate   INTERVAL 1 MONTH),(seconddate   INTERVAL 1 MONTH) FROM cte1 WHERE firstdate < '2022-12-01' AND seconddate < '2023-12-01'
)
SELECT CONCAT(firstdate, ' to ',seconddate) as FullRange FROM cte1;

If we run it, It returns:

..............