Skip to Main Content
  • Questions
  • need a sql to find night work hours between two dates. night work hours are 10:00 PM to next day morning 06:00 AM

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: June 11, 2024 - 4:46 pm UTC

Last updated: June 24, 2024 - 4:55 am UTC

Version: R12

Viewed 1000+ times

You Asked

Hi Tom,

Need your kind help to check and advise. i need to find number of hours employee worked between 10:00 PM to next day 06:00 AM between two days.

Ex:
1. employee work schedule is 11-MAY-2012 16:00:00 to 12-MAY-2012 09:00:00
Ans: Night hours would be 8 hours
2 hours on 11th
6 hours on 12th

2. employee work schedule is 11-MAY-2012 08:00:00 to 11-MAY-2012 16:00:00
Ans: Night hours would be 8 hours

3. employee work schedule is 11-MAY-2012 20:00:00 to 12-MAY-2012 04:00:00
Ans: Night hours would be 6 hours
2 hours on 11th
4 hours on 12th


Best Regards,
RG

and Chris said...

Here's one way to approach it:

If the shift started and finished on the same day, add how many hours before 6am (greatest 6 minus start hour and zero) they worked to the number after 10pm (greatest of end hour minus 22 (10pm) and zero)
If they worked overnight, add the hours worked after 10pm (24 minus greatest of start hour and 22) to the greatest of their finish time and 6.

Giving something like this:

with vals as (
  select '11-MAY-2012 16:00:00' st, '12-MAY-2012 09:00:00' en from dual 
  union all 
  select '11-MAY-2012 08:00:00' st, '11-MAY-2012 16:00:00' en from dual
  union all 
  select '11-MAY-2012 20:00:00' st, '12-MAY-2012 04:00:00' en from dual
), dts as (
select 
  to_date ( st, 'dd-mon-yyyy hh24:mi:ss' ) st_dt,
  to_date ( en, 'dd-mon-yyyy hh24:mi:ss' ) en_dt
from   vals
)
select dts.*,
  case 
    when extract ( day from st_dt ) = extract ( day from en_dt ) then 
      greatest ( 6 - to_number ( to_char ( st_dt, 'hh24' ) ), 0 ) +
      greatest ( to_number ( to_char ( en_dt, 'hh24' ) ) - 22, 0 ) 
    else 
      24 - greatest ( to_number ( to_char ( st_dt, 'hh24' ) ), 22 ) +
      least ( to_number ( to_char ( en_dt, 'hh24' ) ), 6 ) 
  end night_time
from dts;

ST_DT                EN_DT                NIGHT_TIME
-------------------- -------------------- ----------
11-MAY-2012 16:00:00 12-MAY-2012 09:00:00          8
11-MAY-2012 08:00:00 11-MAY-2012 16:00:00          0
11-MAY-2012 20:00:00 12-MAY-2012 04:00:00          6


I get zero hours for example 2; I don't understand why you say their night hours are 8. 8am -> 4pm on the same day => daytime only hours, right?

This assumes that everyone starts & finishes on the hour. This is unlikely to be the case! Update the calculations above to use minutes instead of hours if you need to handle this.

It also assumes no one works an 18+ hour shift that starts before 6am. If they are the company is probably breaking some labour laws - and you'll have to update the calculation.

Rating

  (5 ratings)

Comments

A reader, June 13, 2024 - 1:46 pm UTC

Sorry my bad. You are right for second scenario it would be 0 hours

A reader, June 13, 2024 - 1:48 pm UTC

Thanks and appreciated Chris. This would be a great help. I pick very lengthy solution and ran into performance issue. This is very straight forward and simple.
Connor McDonald
June 14, 2024 - 3:25 am UTC

glad we could help

A reader, June 14, 2024 - 1:24 pm UTC

Hi Chris, I have another example where i am expecting 0.5 value. Could you please kindly help to check advise.

ex: '11-MAY-2012 08:00:00' st, '11-MAY-2012 22:30:00'

for above scenario, it should return 0.5 (30 mins) night hours
but sql is giving 0 hours.
Chris Saxon
June 14, 2024 - 4:58 pm UTC

Like I said, this only handles whole hours. To handle fractions, you need to convert the values to minutes. e.g. 6am = 360 minutes. Then divide the working minutes by 60 to go back to hours

A reader, June 17, 2024 - 8:09 am UTC

Thanks Chris. I have converted into mins and is work as expected.

with vals as (
select '11-MAY-2012 08:00:00' st, '11-MAY-2012 22:45:00' en from dual
union all
select '11-MAY-2012 08:00:00' st, '11-MAY-2012 16:00:00' en from dual
union all
select '11-MAY-2012 20:00:00' st, '12-MAY-2012 04:00:00' en from dual
), dts as (
select
to_date ( st, 'dd-mon-yyyy hh24:mi:ss' ) st_dt,
to_date ( en, 'dd-mon-yyyy hh24:mi:ss' ) en_dt
from vals
)
select dts.*,
(CASE
WHEN EXTRACT ( DAY FROM st_dt ) = EXTRACT ( DAY FROM en_dt ) THEN
GREATEST ( (6*60) - (REGEXP_SUBSTR(to_char(st_dt,'hh24:mi'),'[^:]+',1,1)*60 + REGEXP_SUBSTR(to_char(st_dt,'hh24:mi'),'[^:]+',1,2)) , 0 ) +
GREATEST ( (REGEXP_SUBSTR(to_char(en_dt,'hh24:mi'),'[^:]+',1,1)*60 + REGEXP_SUBSTR(to_char(en_dt,'hh24:mi'),'[^:]+',1,2)) - 22*60, 0 )
ELSE
(24*60) - GREATEST ( (REGEXP_SUBSTR(to_char(st_dt,'hh24:mi'),'[^:]+',1,1)*60 + REGEXP_SUBSTR(to_char(st_dt,'hh24:mi'),'[^:]+',1,2)) , (22*60) ) +
LEAST ( (REGEXP_SUBSTR(to_char(en_dt,'hh24:mi'),'[^:]+',1,1)*60 + REGEXP_SUBSTR(to_char(en_dt,'hh24:mi'),'[^:]+',1,2)), (6*60) )
END)/60 night_time
from dts;
Connor McDonald
June 18, 2024 - 4:15 am UTC

nice work

More a math problem than a programming one

mathguy, June 23, 2024 - 3:06 am UTC

There are several ways to solve this problem; in each case, once a mathematical solution has been chosen, writing the code is straightforward.

Here is how I would solve this. The formula for computing the number of night-shift hours between ST_DT and EN_DT is

8 * (trunc(en_dt + 2/24) - trunc(st_dt + 2/24))
+ least(8, 24 * (en_dt + 2/24 - trunc(en_dt + 2/24)))
- least(8, 24 * (st_dt + 2/24 - trunc(st_dt + 2/24)))


Adding 2/24 to the dates is a standard shift, to simplify the problem; the night shift is from 22:00 to 06:00, or - after adding two hours - it becomes from 00:00 to 08:00. (A cute, but not really helpful, way to think about this is that we convert the date-times to the time zone two hours ahead of us, to achieve this simplification).

Then the first term credits 8 hours for every full day from ST_DT to EN_DT, including ST_DT but excluding EN_DT. The second term adds the night-shift hours for EN_DT (the +2/24 trick makes this second term very simple), and similarly the last term adjusts for the night-shift hours before the time-of-day of ST_DT.
Connor McDonald
June 24, 2024 - 4:55 am UTC

nice stuff