Skip to Main Content
  • Questions

  • Calculate overlapping time intervals

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jason.

Asked: April 18, 2016 - 8:51 pm UTC

Last updated: April 19, 2016 - 1:35 pm UTC

Version: 10.2.0.3.0

Viewed 1000+ times

You Asked

I'm working with a timekeeping application and we have several business rules that apply to night shift hours (hours worked between 1900 and 0700). For each day we have an in and out time for each employee. I need to determine the number of hours between 1900 and 0700.

Example:
CREATE TABLE time_sheets(
  id                             NUMBER NOT NULL,
  person_id                      NUMBER NOT NULL,
  start_time                     DATE,
  end_time                       DATE,
  time_code                      VARCHAR2(10)
  );

INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE) 
VALUES(1,50,TO_DATE('4/18/2016 19:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/19/2016 07:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');
INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE) 
VALUES(2,50,TO_DATE('4/19/2016 17:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/19/2016 05:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');
INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE) 
VALUES(3,50,TO_DATE('4/20/2016 21:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/21/2016 02:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');
INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE) 
VALUES(4,50,TO_DATE('4/21/2016 21:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/22/2016 09:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');
INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE) 
VALUES(5,50,TO_DATE('4/22/2016 17:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/23/2016 09:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');

COMMIT;


If the question is, "how many hours between 1900 and 0700 did employee 50 work on each day", I expect the following answers
4/18/2016 - 12 hours
4/19/2016 - 10 hours
4/20/2016 - 5 hours
4/21/2016 - 10 hours
4/22/2016 - 12 hours

How can I write a query to accomplish this with the given start and stop times?

and Connor said...

Can you clarify the second insert for me ?

INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE)
VALUES(2,50,TO_DATE('4/19/2016 17:00:00','MM/DD/RRRR
HH24:MI:SS'),TO_DATE('4/19/2016 05:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');

But without that, is this the kind of thing you were after ?


SQL> drop table time_sheets purge;

Table dropped.

SQL>
SQL> CREATE TABLE time_sheets(
  2    id                             NUMBER NOT NULL,
  3    person_id                      NUMBER NOT NULL,
  4    start_time                     DATE,
  5    end_time                       DATE,
  6    time_code                      VARCHAR2(10)
  7    );

Table created.

SQL>
SQL> INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE)
  2  VALUES(1,50,TO_DATE('4/18/2016 19:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/19/2016 07:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');

1 row created.

SQL>
SQL> --INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE)
SQL> --VALUES(2,50,TO_DATE('4/19/2016 07:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/19/2016 17:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');
SQL>
SQL> INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE)
  2  VALUES(3,50,TO_DATE('4/20/2016 21:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/21/2016 02:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');

1 row created.

SQL>
SQL> INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE)
  2  VALUES(4,50,TO_DATE('4/21/2016 21:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/22/2016 09:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');

1 row created.

SQL>
SQL> INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE)
  2  VALUES(5,50,TO_DATE('4/22/2016 17:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/23/2016 09:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> @longdate

Session altered.

SQL>
SQL> select
  2    t.*,
  3    to_char(trunc(t.start_time),'DD/MM') nominal_day,
  4    24*(t.END_TIME - t.start_time) hrs,
  5    greatest(19-to_number(to_char(start_time,'HH24')),0) adjust_start,
  6    greatest(to_number(to_char(end_time,'HH24'))-07,0) adjust_end
  7  from time_sheets t;

        ID  PERSON_ID START_TIME          END_TIME            TIME_CODE  NOMIN        HRS ADJUST_START ADJUST_END
---------- ---------- ------------------- ------------------- ---------- ----- ---------- ------------ ----------
         1         50 18/04/2016 19:00:00 19/04/2016 07:00:00 TC1        18/04         12            0      0
         3         50 20/04/2016 21:00:00 21/04/2016 02:00:00 TC1        20/04          5            0      0
         4         50 21/04/2016 21:00:00 22/04/2016 09:00:00 TC1        21/04         12            0      2
         5         50 22/04/2016 17:00:00 23/04/2016 09:00:00 TC1        22/04         16            2      2

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jason Bolen, April 19, 2016 - 1:15 pm UTC

Apologies on the second insert statement, it should have been:
INSERT INTO time_sheets (ID,PERSON_ID,START_TIME,END_TIME,TIME_CODE) 
VALUES(2,50,TO_DATE('4/19/2016 17:00:00','MM/DD/RRRR HH24:MI:SS'),TO_DATE('4/20/2016 
05:00:00','MM/DD/RRRR HH24:MI:SS'),'TC1');


I'm still working though your query's logic, but would this select be a reasonable way to get the number of hours per day between 1900 and 0700? The results are what I would expect.
SELECT z.*
  , z.hrs - (z.adjust_start + z.adjust_end) between_1900_0700
FROM (
  SELECT t.*
    , to_char(trunc(t.start_time),'DD/MM') nominal_day
    , 24 * (t.end_time - t.start_time) hrs
    , greatest(19 - to_number(to_char(start_time,'HH24')), 0) adjust_start
    , greatest(to_number(to_char(end_time,'HH24')) - 7, 0) adjust_end
  FROM time_sheets t) z


Care to share your thought process about 19 minus the start time and 7 plus the end time? Before I submitted this question, I was attempting something similar. I was finding the GREATEST of the start time and 1900 and the LEAST of the end time and 0700 then doing the subtraction to find the number of hours between 1900 and 0700. It wasn't working exactly how I wanted, your approach seems more simple.
Connor McDonald
April 19, 2016 - 1:35 pm UTC

Your solution is what my intent would be, ie, take the difference between the dates and then factor in the "adjustment" hours.

Which I think meets the requirement *as per the question*, but I thinking more work might be required in the general case, where people start shifts at different times etc.

That has been left as a reader exercise :-)

Does this helps ?

Rajeshwaran Jeyabal, April 20, 2016 - 6:22 am UTC

With you new insert for the second row (id=2) in place.

rajesh@ORA12C> select * from t;

        ID  PERSON_ID START_TIME           END_TIME             TIME_CODE
---------- ---------- -------------------- -------------------- ----------
         1         50 18-APR-2016 19:00:00 19-APR-2016 07:00:00 TC1
         2         50 19-APR-2016 17:00:00 20-APR-2016 05:00:00 TC1
         3         50 20-APR-2016 21:00:00 21-APR-2016 02:00:00 TC1
         4         50 21-APR-2016 21:00:00 22-APR-2016 09:00:00 TC1
         5         50 22-APR-2016 17:00:00 23-APR-2016 09:00:00 TC1

5 rows selected.

rajesh@ORA12C> select id,person_id,start_time,end_time,
  2      greatest( start_time ,trunc(start_time) + 19/24) as new_start_time,
  3      least( end_time , trunc(end_time) + 7/24) as new_end_time
  4  from t ;

        ID  PERSON_ID START_TIME           END_TIME             NEW_START_TIME       NEW_END_TIME
---------- ---------- -------------------- -------------------- -------------------- --------------------
         1         50 18-APR-2016 19:00:00 19-APR-2016 07:00:00 18-APR-2016 19:00:00 19-APR-2016 07:00:00
         2         50 19-APR-2016 17:00:00 20-APR-2016 05:00:00 19-APR-2016 19:00:00 20-APR-2016 05:00:00
         3         50 20-APR-2016 21:00:00 21-APR-2016 02:00:00 20-APR-2016 21:00:00 21-APR-2016 02:00:00
         4         50 21-APR-2016 21:00:00 22-APR-2016 09:00:00 21-APR-2016 21:00:00 22-APR-2016 07:00:00
         5         50 22-APR-2016 17:00:00 23-APR-2016 09:00:00 22-APR-2016 19:00:00 23-APR-2016 07:00:00

5 rows selected.

rajesh@ORA12C> select id,person_id,start_time,end_time,
  2    mod( new_end_time - new_start_time ,1)*24 as diff
  3  from (
  4  select id,person_id,start_time,end_time,
  5      greatest( start_time ,trunc(start_time) + 19/24) as new_start_time,
  6      least( end_time , trunc(end_time) + 7/24) as new_end_time
  7  from t
  8       )
  9  order by id
 10  /

        ID  PERSON_ID START_TIME           END_TIME                   DIFF
---------- ---------- -------------------- -------------------- ----------
         1         50 18-APR-2016 19:00:00 19-APR-2016 07:00:00         12
         2         50 19-APR-2016 17:00:00 20-APR-2016 05:00:00         10
         3         50 20-APR-2016 21:00:00 21-APR-2016 02:00:00          5
         4         50 21-APR-2016 21:00:00 22-APR-2016 09:00:00         10
         5         50 22-APR-2016 17:00:00 23-APR-2016 09:00:00         12

5 rows selected.

rajesh@ORA12C>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.