-1

Is there a way in Oracle SQL of finding out the number of WORKING hours and minutes between two dates?

As an example

The working day is Monday - Fri 8am-5pm

Job.Job_Logged     Job.Actual_Start_Date       Time Elapsed
01/08/2019 10:00   01/08/2019  12:32           2:32
01/08/2019 16:00   02/08/2019  09:00           3:00
Rob Morris
  • 137
  • 6

1 Answers1

1

I did not check for possible duplicates, but one way to do it is recursive query:

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) hours
  from cte
  where to_char(hd1, 'd') not in (6, 7)
  group by id, start_date, end_date

dbfiddle demo

Where I use 17/24 it means ending hour 17:00, 8/24 - starting hour, not in (6, 7) excludes saturdays and sundays.

Edit 1: It should be 24 * 60 * 60 in last select.

Edit 2: To make query independent of nls_settings use:

to_char(hd1, 'Dy', 'nls_date_language=english') not in ('Sat', 'Sun')

dbfiddle demo

the version of Oracle I'm using insists on having SELECT as the first word in any code

Such recursive queries are available from Oracle version 11. I don't know if your tool requires select in the first line or is this version problem, but in first case you can move RCTE to from clause:

select id, start_date, end_date, dbms_xplan.format_time_s(sum(hd2 - hd1) * 24 * 60 * 60) hours
  from (
    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 * from cte 
       where to_char(hd1, 'Dy', 'nls_date_language=english') not in ('Sat', 'Sun') )
  group by id, start_date, end_date

fiddle

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • 1
    You need to be careful with day numbers as they depend on NLS territory - [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3a52fcb0fdeb060f8a6f45831f6793c9). I'm not sure why the answer for ID 2 differs though; it should be 2 hours though, right? I'm not sure why it's only 1 in your original. (Even [after your edit](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ffc266bcf2f0bfd40e99710ff6f95334).) – Alex Poole Aug 02 '19 at 15:02
  • @AlexPoole Yes, nls_territory is important. And yes, 2 hours, 16-17 and 8-9. – Ponder Stibbons Aug 02 '19 at 15:04
  • Thanks for this but the version of Oracle I'm using insists on having SELECT as the first word in any code. – Rob Morris Aug 02 '19 at 15:16
  • @RobMorris - the version of Oracle (i.e. pre-9i), or the client/application you are using? If the latter you might be able to wrap the whole thing in a `select * from ( ... )` hack. If the former then you can use a hierarchical query (in an inline view) instead of the recursive CTE. (It's helpful to include any restrictions in your question...) – Alex Poole Aug 02 '19 at 15:26
  • I think its more the application that it is being used in (Confirm) as the version is 11g. I've tried looking online for how to create an inline view from a CTE but it's showing nothing and the previous example looks nothing like this one in terms of syntax – Rob Morris Aug 02 '19 at 15:43
  • As you can see in the fiddles both queries works and we do not know your application. – Ponder Stibbons Aug 02 '19 at 15:44
  • Thank you @PonderStibbons - thats got it working now. I'm trying to get to grips with Oracle and finding some things a lot easier than others! – Rob Morris Aug 02 '19 at 15:58