Oracle 18c

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

create table data(
fromTime DATE,
toTime DATE);
insert into data(fromTime, toTime)
values(TO_DATE('2003/05/03 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2003/05/03 02:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected

insert into data(fromTime, toTime)
values(TO_DATE('2003/05/03 04:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2003/05/03 05:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected

insert into data(fromTime, toTime)
values(TO_DATE('2003/05/03 15:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2003/05/03 18:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected

insert into data(fromTime, toTime)
values(TO_DATE('2004/06/03 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2004/06/03 23:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected

insert into data(fromTime, toTime)
values(TO_DATE('2004/06/03 23:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2004/06/04 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected

insert into data(fromTime, toTime)
values(TO_DATE('2004/05/03 04:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2004/05/03 05:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected


insert into data(fromTime, toTime)
values(TO_DATE('2003/06/03 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),
TO_DATE('2003/06/03 02:00:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected

SELECT * from data;


FROMTIME
TOTIME


03-MAY-03
03-MAY-03

03-MAY-03
03-MAY-03

03-MAY-03
03-MAY-03

03-JUN-04
03-JUN-04

03-JUN-04
04-JUN-04

03-MAY-04
03-MAY-04

03-JUN-03
03-JUN-03


SELECT to_char(fromTime,'DD/MM/YYYY'), sum((toTime-fromTime)*24) from data
group by to_char(fromTime,'DD/MM/YYYY');


TO_CHAR(FROMTIME,'DD/MM/YYYY')
SUM((TOTIME-FROMTIME)*24)


03/05/2003
6

03/05/2004
1

03/06/2004
24

03/06/2003
2


SELECT to_char(fromTime,'DD/MM/YYYY'), 
case when sum((toTime-fromTime)*24) = 24 then 'YES' else 'NO' END as is24
from data
group by to_char(fromTime,'DD/MM/YYYY');


TO_CHAR(FROMTIME,'DD/MM/YYYY')
IS24


03/05/2003
NO

03/05/2004
NO

03/06/2004
YES

03/06/2003
NO

FROMTIME	TOTIME
03-MAY-03	03-MAY-03
03-MAY-03	03-MAY-03
03-MAY-03	03-MAY-03
03-JUN-04	03-JUN-04
03-JUN-04	04-JUN-04
03-MAY-04	03-MAY-04
03-JUN-03	03-JUN-03