Oracle, SQL, how to get intervals between dates

Heh. If you really wish it :)

Fifth record (started at 2016-10-30) had been added for testing purposes.

SQL> with
  2    src as (select timestamp '2017-11-16 21:25:00' b, timestamp '2017-11-16 22:55:00' f from dual union all
  3            select timestamp '2017-11-17 05:45:00' b, timestamp '2017-11-17 07:05:00' f from dual union all
  4            select timestamp '2017-11-18 23:00:00' b, timestamp '2017-11-19 1:10:00' f from dual union all
  5            select timestamp '2017-11-17 18:00:00' b, timestamp '2017-11-17 19:00:00' f from dual union all
  6            select timestamp '2016-10-30 00:00:00' b, timestamp '2016-11-03 23:00:00' f from dual),
  7    srd as (select b, f, f - b t from src),
  8    mmm as (select min(trunc(b)) b, max(trunc(f)) f from src),
  9    rws as (select b + 6/24 + rownum - 1 b, b + 22/24 + rownum - 1 f from mmm connect by level <= f - b + 1),
 10    mix as (select s.b, s.f, s.t, r.b rb, r.f rf from srd s, rws r where s.f >= r.b (+) and r.f (+) >= s.b),
 11    clc as (select b, f, t, nvl(numtodsinterval(sum((least(f, rf) + 0) - (greatest(b, rb) + 0)), 'DAY'), interval '0' second) d from mix group by b, f, t)
 12  select
 13    to_char(b, 'dd.mm.yyyy hh24:mi') as "datetime begin",
 14    to_char(f, 'dd.mm.yyyy hh24:mi') as "datetime finish",
 15    cast(t as interval day to second(0)) as "total time",
 16    cast(d as interval day to second(0)) as "daytime",
 17    cast(t - d as interval day to second(0)) as "nighttime"
 18  from
 19    clc
 20  order by
 21    1, 2;

datetime begin     datetime finish    total time     daytime        nighttime
------------------ ------------------ -------------- -------------- --------------
16.11.2017 21:25   16.11.2017 22:55   +00 01:30:00   +00 00:35:00   +00 00:55:00
17.11.2017 05:45   17.11.2017 07:05   +00 01:20:00   +00 01:05:00   +00 00:15:00
17.11.2017 18:00   17.11.2017 19:00   +00 01:00:00   +00 01:00:00   +00 00:00:00
18.11.2017 23:00   19.11.2017 01:10   +00 02:10:00   +00 00:00:00   +00 02:10:00
30.10.2016 00:00   03.11.2016 23:00   +04 23:00:00   +03 08:00:00   +01 15:00:00

A different approach is more brute force one, but it allows to distinct the interval configuration from the reporting. It goes in three stept:

1) define the rate type for aech minute of the day (change the granularity if required)

create table day_config as
with helper as (
select 
rownum -1 minute_id 
from dual connect by level <= 24*60),
helper2 as (
select 
minute_id,
trunc(minute_id/60) hour_no,
mod(minute_id,60) minute_no
from helper) 
select
minute_id,hour_no, minute_no,
case when hour_no >= 22 or hour_no <= 5 then 0 else 1 end rate_id
from helper2;

select * from day_config order by minute_id;

 MINUTE_ID    HOUR_NO  MINUTE_NO    RATE_ID
---------- ---------- ---------- ----------
         0          0          0          0 
         1          0          1          0 
         2          0          2          0 
         3          0          3          0 
         4          0          4          0 
         5          0          5          0 
         6          0          6          0 
         7          0          7          0 
         8          0          8          0 
         9          0          9          0 

Here rate_id means nigth, rate_id 1 means a day. Advantage is, that you can introduce as much rate types as required.

2) expand the configuration for the required interval e.g. to whole year. So now we have for each minute of the year the configuration, which rate is to be applied.

create or replace view year_config as
select my_date + MINUTE_ID / (24*60) minute_ts , MINUTE_ID, HOUR_NO, MINUTE_NO, RATE_ID from day_config 
cross join 
(select DATE '2017-01-01' + rownum -1 as my_date from dual connect by level <= 365)
order by 1,2;

select * from (
select * from year_config
order by 1)
where rownum <= 5;

MINUTE_TS            MINUTE_ID    HOUR_NO  MINUTE_NO    RATE_ID
------------------- ---------- ---------- ---------- ----------
01-01-2017 00:00:00          0          0          0          0 
01-01-2017 00:01:00          1          0          1          0 
01-01-2017 00:02:00          2          0          2          0 
01-01-2017 00:03:00          3          0          3          0 
01-01-2017 00:04:00          4          0          4          0

3) the reporting is as easy as joining to our config table constraining the interval (half open) and grouping in the RATE.

select b, f,RATE_ID, count(*) minute_cnt 
from tst join year_config c on c.MINUTE_TS >= tst.b and c.MINUTE_TS < tst.f 
group by b, f,RATE_ID
order by b, f,RATE_ID;

B                   F                      RATE_ID MINUTE_CNT
------------------- ------------------- ---------- ----------
16-11-2017 21:25:00 16-11-2017 22:55:00          0         55 
16-11-2017 21:25:00 16-11-2017 22:55:00          1         35 
17-11-2017 05:45:00 17-11-2017 07:05:00          0         15 
17-11-2017 05:45:00 17-11-2017 07:05:00          1         65 
17-11-2017 18:00:00 17-11-2017 19:00:00          1         60 
18-11-2017 23:00:00 19-11-2017 01:10:00          0        130

The easiest way is probably to get all minutes worked in a recursive WITH clause and then see in which time range the minutes fall. As Oracle doesn't have a TIME datatype unfortunately, we'll have to work with times strings ('00'00' till '23:59').

with shifts as
(
  select 'night' as shift,  '00:00' as starttime, '05:59' as endtime, 20 as cost from dual
  union all
  select 'normal' as shift, '06:00' as starttime, '21:59' as endtime, 17 as cost from dual
  union all
  select 'night' as shift,  '22:00' as starttime, '23:59' as endtime, 20 as cost from dual
)
, workminutes(wo, workminute, thetime, endtime) as
(
  select wo, to_char(starttime, 'hh24:mi') as workminute, starttime as thetime, endtime
  from mytable
  union all
  select 
    wo, 
    to_char(thetime + interval '1' minute, 'hh24:mi') as workminute,
    thetime + interval '1' minute as thetime,
    endtime
  from workminutes
  where thetime + interval '1' minute < endtime
)
select 
  wo, 
  count(case when s.shift = 'normal' then 1 end) as normal_time,
  coalesce(sum(case when m.workminute between '06:00' and '21:59' then s.cost end), 0)
    as normal_cost,
  count(case when s.shift = 'night' then 1 end) as night_time,
  coalesce(sum(case when m.workminute not between '06:00' and '21:59' then s.cost end), 0)
    as night_cost,
  count(*) as total_time,
  coalesce(sum(s.cost), 0)
    as total_cost
from workminutes m
join shifts s on m.workminute between s.starttime and s.endtime
group by wo
order by wo;

Output:

WO   NORMAL_TIME   NORMAL_COST   NIGHT_TIME   NIGHT_COST   TOTAL_TIME   TOTAL_COST   
21   35            595           55           1100         90           1695         
22   65            1105          15           300          80           1405         
23   0             0             130          2600         130          2600         
24   60            1020          0            0            60           1020         
25   4800          81600         2340         46800        7140         128400       

(This query looks a lot nicer of course, if you have a real shifts table and don't have to make one up on-the-fly. Also, you may not need all those seven columns I have in my result.)