I have a time series in MySQL and need to write a cycle as described by the pseudocode below:
FOR DATETIME BETWEEN DATE1 AND DATE2 WITH STEP OF 1 MIN INSERT INTO TABLE X ('TIMESTAMP', 'COLUMN1', 'COLUMN2') VALUES (DATETIME, NULL, NULL); ENDFOR
how do I write this, optimally, in MySQL?
Advertisement
Answer
You can use a recursive CTE:
insert into x (timestamp, column1, column2) with recursive cte as ( select @date1 as dte union all select dte + interval 1 minute from cte where dte < @date2 ) select dt, null, null from cte;
Here is a db<>fiddle.