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.
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.
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;
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.