How to cut one row to starttime and endtime to mutiple day spend hour rows

Question:

How to cut one row to starttime and endtime to mutiple day spend hour rows

Table Data and DDL:

starttime           , endtime
2019/05/03 08:00:00 , 2019/05/05 12:00:00

CREATE TABLE T  ("starttime" timestamp, "endtime" timestamp);
INSERT  INTO T ("starttime", "endtime") VALUES ('03-May-2019 08:00:00 AM', '05-May-2019 12:00:00 PM');

Expected Result:

Date        , SpendHour
2019/05/03 , 16
2019/05/04 , 24
2019/05/05 , 12

Online Demo Link | DB Fiddle

Answer

You can use connect by level <= syntax combined with case..when statements

select trunc(t.starttime+level-1) as "Date", 
       case 
       when trunc(t.starttime+level-1) = trunc(t.starttime) then
            extract(hour from trunc(t.starttime+level)
                  -greatest(t.starttime,trunc(t.starttime+level-1)))     
       when trunc(t.starttime+level-1) = trunc(t.endtime) then     
            extract(hour from t.endtime-trunc(t.endtime))
       else
            24     
       end as "Spend hour"   
  from t
 connect by level <= extract(day from t.endtime - t.starttime)+1;

Demo