MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   cid         INTEGER  NOT NULL PRIMARY KEY 
  ,sid         INTEGER  NOT NULL
  ,type        VARCHAR(11) NOT NULL
  ,day         VARCHAR(7) NOT NULL
  ,time        VARCHAR(20)  NOT NULL
  ,building    VARCHAR(9) NOT NULL
  ,room_number INTEGER  NOT NULL
);
INSERT INTO mytable(cid,sid,type,day,time,building,room_number) VALUES (1,1,'daytime','mon','6,7,8','sky',507);
INSERT INTO mytable(cid,sid,type,day,time,building,room_number) VALUES (2,2,'daytime','thu','2,3,4','nuri',906);
-- INSERT INTO mytable(cid,sid,type,day,time,building,room_number) VALUES (3,3,'daytime','tue','6,7,8','nuri',906);
select * from mytable


cid
sid
type
day
time
building
room_number


1
1
daytime
mon
6,7,8
sky
507

2
2
daytime
thu
2,3,4
nuri
906


with recursive 
    cte as (
        select cid, sid, type, day, room_number,
            substring(time, 1, locate(',', time) - 1) time,
            substring(concat(time, ','), locate(',', time) + 1) rest
        from mytable
        union all
        select cid, sid, type, day, room_number,
            substring(rest, 1, locate(',', rest) - 1),
            substring(rest, locate(',', rest) + 1)
        from cte
        where  locate(',', rest) > 0
    ),
    all_times as (
        select 1 time 
        union all select time + 1 from all_times where time < 13
    )
select  t.cid, t.sid, t.type, t.day, at.time, t.building, t.room_number
from all_times at
cross join mytable t
left join cte c on c.cid = t.cid and c.time = at.time
where c.cid is null
order by t.cid, at.time


cid
sid
type
day
time
building
room_number


1
1
daytime
mon
1
sky
507

1
1
daytime
mon
2
sky
507

1
1
daytime
mon
3
sky
507

1
1
daytime
mon
4
sky
507

1
1
daytime
mon
5
sky
507

1
1
daytime
mon
9
sky
507

1
1
daytime
mon
10
sky
507

1
1
daytime
mon
11
sky
507

1
1
daytime
mon
12
sky
507

1
1
daytime
mon
13
sky
507

2
2
daytime
thu
1
nuri
906

2
2
daytime
thu
5
nuri
906

2
2
daytime
thu
6
nuri
906

2
2
daytime
thu
7
nuri
906

2
2
daytime
thu
8
nuri
906

2
2
daytime
thu
9
nuri
906

2
2
daytime
thu
10
nuri
906

2
2
daytime
thu
11
nuri
906

2
2
daytime
thu
12
nuri
906

2
2
daytime
thu
13
nuri
906

cid	sid	type	day	time	building	room_number
1	1	daytime	mon	1	sky	507
1	1	daytime	mon	2	sky	507
1	1	daytime	mon	3	sky	507
1	1	daytime	mon	4	sky	507
1	1	daytime	mon	5	sky	507
1	1	daytime	mon	9	sky	507
1	1	daytime	mon	10	sky	507
1	1	daytime	mon	11	sky	507
1	1	daytime	mon	12	sky	507
1	1	daytime	mon	13	sky	507
2	2	daytime	thu	1	nuri	906
2	2	daytime	thu	5	nuri	906
2	2	daytime	thu	6	nuri	906
2	2	daytime	thu	7	nuri	906
2	2	daytime	thu	8	nuri	906
2	2	daytime	thu	9	nuri	906
2	2	daytime	thu	10	nuri	906
2	2	daytime	thu	11	nuri	906
2	2	daytime	thu	12	nuri	906
2	2	daytime	thu	13	nuri	906