Hello community how are u doing. Lets say i have this row in a table

So id like to create a view where i give the id as condition and it returns me rows from the field start and adding interval1 interval2 until the field end. In this example (12:00:00 (55 5)) it would return

Im pretty new with mysql and ive been struggling for hours. Thank u all!

CodePudding user response：

For mysql v8, recursive CTE can be used -

with recursive cte(id, starttime,end_1,interval1,interval2) as 
(select * from table_1 union all
select id, addtime(starttime,SEC_TO_TIME((interval1   interval2)*60)) starttime, end_1, interval1, interval2
from cte
where starttime < end_1)
select * from cte;

DB fiddle here.

CodePudding user response：

Assuming, that in each row interval1 and interval2 may have different values, and we want to display results for more then one ID, or even create a view, only LATERAL can do the job for each row separately. It was added in 8.0.14.

create view periods as
select mytable.id, addtime(start,SEC_TO_TIME(a*(interval1   interval2)*60)) starttime, end, interval1, interval2 from mytable, lateral (
with recursive cte as (
   select 0 as a 
   union all 
   select 1 a from cte where a 1<=time_to_sec(subtime(end,start))/((interval1 interval2)*60))
select * from cte
) b

and after that

select * from periods where id = 3 order by starttime

DB Fiddle