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
'info1' as id
, to_date('2021-05-01 10:20:30') as dt_from
, to_date('2021-05-03 07:08:09') as dt_to
from dual
union all
select
'info2'
, to_date('2021-05-02 05:06:07')
, to_date('2021-05-02 10:20:30')
from dual
)
, b(id, dt_from, dt_to, dt) as (
select
id
, dt_from
, dt_to
, trunc(dt_from)
from a
union all
select
id
, dt_from
, dt_to
, dt + 1
from b
where dt < trunc(dt_to)
)
select
id
, dt
, numtodsinterval(
case
| ID | DT | DUR_INTERVAL | DUR_HOURS | HOUR_FROM | HOUR_TO |
|---|---|---|---|---|---|
| info1 | 2021-05-01 00:00:00 | +000000000 13:39:30.000000000 | 13 | 10:20 | 23:59 |
| info1 | 2021-05-02 00:00:00 | +000000001 00:00:00.000000000 | 24 | 00:00 | 23:59 |
| info1 | 2021-05-03 00:00:00 | +000000000 07:08:09.000000000 | 7 | 00:00 | 07:08 |
| info2 | 2021-05-02 00:00:00 | +000000000 05:14:23.000000000 | 5 | 05:06 | 10:20 |