Oracle, SQL, how to get intervals between dates

Question

I need help with a problem. Actually, I do not know if it will be possible to solve it directly in SQL.

I have a list of works. Each work has a start date and ending date, with this format

YYYY/MM/DD  HH24:MI:SS

I need to calculate the cost of those jobs, the hour price depends on the time intervals in which the work has been done:

Nigth time: 22:00 to 6:00, for example: 20 €/h
Normal time: the rest 17 €/h

So, if I have a sample like this:

wo     start                 end
21    2017/11/16 21:25:00    2017/11/16 22:55:00
22    2017/11/17 05:45:00    2017/11/17 07:05:00
23    2017/11/18 23:00:00    2017/11/19 1:10:00
24    2017/11/17 18:00:00    2017/11/17 19:00:00

I would need to calculate the intervals of the dates between the 22h and 6h and the rest to multiply them by their corresponding price

wo     rest(minutes)   night(minutes)
21      35              55
22      15              65
23       0              130
24       1               0

Thank for your help in advance.

Answer 1

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