SQL date diff disregarding nights

Question

using an Oracle database, I need to know the difference between any two dates (in seconds), disregarding the interval from 6pm to 8am.

So for example, the difference between 01.11.2011 1pm and 03.11.2011 1pm amounts to 20 hours (or 72000 seconds respectively). I also need to disregard weekends and holidays, but I already got a PLSQL function for that.

Thank you in advance!

Regards, Alex

EDIT
Meanwhile I came up with a solution myself, which is honestly not very sophisticated but does the work:

CREATE OR REPLACE FUNCTION "GET_WORKTIME" 
(
  startdate_in in date,
  enddate_in   in date
)
RETURN NUMBER
AS
  days_between number(4);
  duration number;
  end_of_first_day date;
  start_of_last_day date;
  startdate date;
  enddate date;
  weekday number(1);
  temp_date date;
  holidays day_array;
  is_holiday boolean;
BEGIN
  duration     := 0;
  startdate    := startdate_in;
  enddate      := enddate_in;

IF (startdate IS NULL OR enddate IS NULL OR startdate >= enddate) THEN
  RETURN 0;
END IF;

days_between := trunc(enddate) - trunc(startdate);

end_of_first_day  := to_date(concat(to_char(startdate, 'dd.mm.yyyy'), ' 18:00:00'), 'dd.mm.yyyy hh24:mi:ss');
start_of_last_day := to_date(concat(to_char(enddate,   'dd.mm.yyyy'), ' 08:00:00'), 'dd.mm.yyyy hh24:mi:ss');

temp_date := startdate;

FOR i IN 0..days_between LOOP
    -- if it is the first day, just calculate the time until the end of that day
    IF i = 0 THEN
        duration := duration + greatest((end_of_first_day - startdate), 0) * 24 * 3600;

    -- if it is the last day, just calculate the time until that point in time
    ELSIF i = days_between THEN
        duration := duration + greatest((enddate - start_of_last_day),  0) * 24 * 3600;            

    -- for every other day, simply add 10 hours (6pm - 8am) if it is neither
    -- saturday or sunday nor a holiday
    ELSE
        weekday    := to_number(to_char(temp_date, 'D'));
        is_holiday := false;

        -- weekend?
        IF (NOT weekday IN (6,7)) THEN

            -- holiday?
            FOR j IN extract(year FROM startdate)..extract(year FROM enddate) LOOP
                holidays := get_holidays_for_year(j);

                FOR k IN 1..holidays.count LOOP
                    IF (holidays(k) = trunc(temp_date)) THEN
                        is_holiday := true;
                    END IF;
                END LOOP;
            END LOOP;

            -- add 10 hours to the duration
            IF (NOT is_holiday) THEN
                duration := duration + 10 * 3600;
            END IF;
        END IF;
    END IF;

    temp_date := temp_date + 1;
END LOOP;
RETURN duration;
END;

The function also disregards weekends and holidays, so it looks a little more comprehensive than it actually is. Thank you for your help!

Regards, Alex

Answer 1

It may be easiest to break this into 3 parts:

1. The first partial day (if any)
2. The last partial day (if any)
3. The intervening whole days (if any)

The first partial day is from the later of (start time, 8am) to the earlier of (last_date, 6pm on the first day), so we subtract those to get the time for the first day:

greatest ( least ( trunc(first_date)+18/24
                        , last_date
                        ) - greatest( first_date
                                    , trunc(first_date)+8/24
                                    )
                , 0) * 24 as last_day_hours

The outer greatest function ensures that we don't get a negative value e.g. if start time is after 6pm.

If the last day is not the same as the first day then the last partial day is from 8am to (end time) on the last day:

   case when trunc(last_date) != trunc(first_date) 
        then greatest ( least( last_date
                             , trunc(last_date)+18/24
                             )
                        - (trunc(last_date)+8/24)
                      , 0) * 24
        else 0 end as last_day_hours

The intervening whole days are (in hours):

   greatest ( (trunc(last_date) - (trunc(first_date)+1))
            , 0
            ) * 10 as intervening_days_in_hours

10 being the number of hours between 8am and 6pm

So adding those 3 values together gives the total hours for the period, and multiplying the total by 3600 gives the seconds:

(
   (greatest ( least ( trunc(first_date)+18/24
                    , last_date
                    ) - greatest( first_date
                                , trunc(first_date)+8/24
                                )
            , 0) * 24)
 + case when trunc(last_date) != trunc(first_date) 
        then greatest ( least( last_date
                             , trunc(last_date)+18/24
                             )
                        - (trunc(last_date)+8/24)
                      , 0) * 24
        else 0 end 
 + (greatest ( (trunc(last_date) - (trunc(first_date)+1))
            , 0
            ) * 10)
) * 3600 as total_seconds

I can't help feeling it should be easier than this though! And of course, this does not take weekends into account.

Answer 2

In Oracle you can simply subtract two dates. You can calculate the number of seconds by dividing the result by (24*60*60)

select (last_date - first_date) / (24*60*60) as seconds from table

A trick to format the difference is this:

select to_char(trunc(sysdate) + (last_date - first_date), 'hh24:mi:ss') as time_between from table