Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table jobs(id, start_date, end_date) as (
    select 1, to_date('01/08/2019 10:00', 'dd/mm/yyyy hh24:mi'), to_date('01/08/2019 12:32', 'dd/mm/yyyy hh24:mi') from dual union all
    select 2, to_date('01/08/2019 16:00', 'dd/mm/yyyy hh24:mi'), to_date('02/08/2019 09:00', 'dd/mm/yyyy hh24:mi') from dual union all
    select 3, to_date('01/08/2019 16:00', 'dd/mm/yyyy hh24:mi'), to_date('10/08/2019 14:00', 'dd/mm/yyyy hh24:mi') from dual );
3 rows affected

alter session set nls_territory = 'America'
with cte(id, start_date, end_date, hd1, hd2) as (
    select id, cast(start_date as date), cast(end_date as date), cast(start_date as date), 
           cast(least(end_date, trunc(start_date) + 17/24) as date)
      from jobs 
    union all 
    select id, start_date, end_date, cast(trunc(hd1) + 1 + 8/24 as date), 
           cast(least(trunc(hd1) + 1 + 17/24, end_date) as date)
      from cte
      where trunc(hd1) + 1 + 8/24 < end_date)
select id, start_date, end_date, dbms_xplan.format_time_s(sum(hd2 - hd1) * 24 * 60 * 60) hours
  from cte
  where to_char(hd1, 'Dy', 'nls_date_language=english') not in ('Sat', 'Sun')
  group by id, start_date, end_date


ID
START_DATE
END_DATE
HOURS


1
01-AUG-19
01-AUG-19
02:32:00

3
01-AUG-19
10-AUG-19
55:00:00

2
01-AUG-19
02-AUG-19
02:00:00


alter session set nls_territory = 'United Kingdom'
with cte(id, start_date, end_date, hd1, hd2) as (
    select id, cast(start_date as date), cast(end_date as date), cast(start_date as date), 
           cast(least(end_date, trunc(start_date) + 17/24) as date)
      from jobs 
    union all 
    select id, start_date, end_date, cast(trunc(hd1) + 1 + 8/24 as date), 
           cast(least(trunc(hd1) + 1 + 17/24, end_date) as date)
      from cte
      where trunc(hd1) + 1 + 8/24 < end_date)
select id, start_date, end_date, dbms_xplan.format_time_s(sum(hd2 - hd1) * 24 * 60 * 60) hours
  from cte
  where to_char(hd1, 'Dy', 'nls_date_language=english') not in ('Sat', 'Sun')
  group by id, start_date, end_date


ID
START_DATE
END_DATE
HOURS


1
01-AUG-19
01-AUG-19
02:32:00

3
01-AUG-19
10-AUG-19
55:00:00

2
01-AUG-19
02-AUG-19
02:00:00
ID	START_DATE	END_DATE	HOURS
1	01-AUG-19	01-AUG-19	02:32:00
3	01-AUG-19	10-AUG-19	55:00:00
2	01-AUG-19	02-AUG-19	02:00:00