By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'
with a as (
select
date '2022-04-17'
+ interval '01:30:00' hour to second
+ interval '2' hour * level
as dt
from dual
connect by level < 90
)
select
to_char(dt, 'yyyymmdd') as day_
, listagg(to_char(dt, 'hh24:mi'), ',')
within group (order by dt asc) as hours
from a
where 1 = 1
/*From Mon 08 AM*/
and dt > trunc(dt, 'iw') +
interval '8' hour
/*Till Sat end of the day*/
and dt < trunc(dt, 'iw') + 6
/*and except minutes between 7 and 8 AM*/
and not (
to_char(dt, 'hh24mi') < '0800'
and to_char(dt, 'hh24mi') > '0700'
)
group by to_char(dt, 'yyyymmdd')
| DAY_ | HOURS |
|---|---|
| 20220418 | 09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30 |
| 20220419 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30 |
| 20220420 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30 |
| 20220421 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30 |
| 20220422 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30 |
| 20220423 | 01:30,03:30,05:30,09:30,11:30,13:30,15:30,17:30,19:30,21:30,23:30 |