time range between 2 dates

Question

I have following script

with  first_step as
(
SELECT  
1 as MY_TYPE,
    2373 as my_id
    ,to_date('15.02.23 17:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('17.02.23 12:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
    union all
SELECT  
1 as MY_TYPE,
    2373 as my_id
    ,to_date('16.02.23 14:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('16.02.23 15:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
    union all
SELECT  
0 as MY_TYPE,
    2373 as my_id
    ,to_date('14.02.23 22:00'  , 'dd.mm.yyyy HH24:MI') AS  time_from
    ,to_date('16.02.23 18:00' , 'dd.mm.yyyy HH24:MI')AS  time_till
    from dual
),
second_step as 
(
select 
MY_TYPE,
my_id,
to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy') AS  my_date,
case when trunc(time_from)  < to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy') then '00:00' else to_char(time_from,'HH24:MI') end time_from,
case when trunc(time_till) > to_date(to_char(time_from +(column_value-1), 'dd.mm.yyyy'),'dd.mm.yyyy') then '23:59' else replace(to_char(time_till,'HH24:MI'),'00:00','23:59') end time_till
from first_step 
   CROSS JOIN TABLE ( CAST(MULTISET(
                SELECT
                   level
                from
                    dual
                CONNECT BY time_from  + level-1    <=    time_till
            ) AS sys.odcinumberlist) ) n
)
select * from second_step 
order by 
my_date,time_from, time_till

that is what I get

But I need that

So, we have entries which are on the same day, but also entries lasting multiple days. The single day entries should stay as they are, but the multiple days should be stretched.Currently my multiple days entries are not represented correctly. What is wrong with my script?

Answer 1

You can use a recursive query:

with first_step (my_type, my_id, time_from, time_till) as (
  SELECT 1,
         2373,
         to_date('15.02.2023 17:00', 'dd.mm.yyyy HH24:MI'),
         to_date('17.02.2023 12:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 1,
         2373,
         to_date('16.02.2023 14:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 15:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 0,
         2373,
         to_date('14.02.2023 22:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 18:00', 'dd.mm.yyyy HH24:MI')
  from   dual
),
days (my_type, my_id, time_from, day_end, time_till) AS (
  SELECT my_type,
         my_id,
         time_from,
         TRUNC(time_from) + INTERVAL '23:59:59' HOUR TO SECOND,
         time_till
  FROM   first_step
UNION ALL
  SELECT my_type,
         my_id,
         day_end + INTERVAL '1' SECOND,
         day_end + INTERVAL '1' DAY,
         time_till
  FROM   days
  WHERE  day_end < time_till
)
SELECT my_type,
       my_id,
       time_from,
       LEAST(day_end, time_till) AS time_till
FROM   days
ORDER BY my_id, time_from, time_till;

Or a hierarchical query:

with first_step (my_type, my_id, time_from, time_till) as (
  SELECT 1,
         2373,
         to_date('15.02.2023 17:00', 'dd.mm.yyyy HH24:MI'),
         to_date('17.02.2023 12:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 1,
         2373,
         to_date('16.02.2023 14:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 15:00', 'dd.mm.yyyy HH24:MI')
  from   dual
union all
  SELECT 0,
         2373,
         to_date('14.02.2023 22:00', 'dd.mm.yyyy HH24:MI'),
         to_date('16.02.2023 18:00', 'dd.mm.yyyy HH24:MI')
  from   dual
)
SELECT my_type,
       my_id,
       GREATEST(time_from, day_start) AS time_from,
       LEAST(time_till, day_end) AS time_till
FROM   first_step f
       CROSS JOIN LATERAL (
         SELECT TRUNC(f.time_from) + LEVEL - INTERVAL '1' DAY AS day_start,
                TRUNC(f.time_from) + LEVEL - INTERVAL '1' SECOND AS day_end
         FROM   dual
         CONNECT BY TRUNC(f.time_from) + LEVEL - 1 < f.time_till
       )
ORDER BY my_id, time_from, time_till

Which both output:

MY_TYPE	MY_ID	TIME_FROM	TIME_TILL
0	2373	2023-02-14 22:00:00	2023-02-14 23:59:59
0	2373	2023-02-15 00:00:00	2023-02-15 23:59:59
1	2373	2023-02-15 17:00:00	2023-02-15 23:59:59
0	2373	2023-02-16 00:00:00	2023-02-16 18:00:00
1	2373	2023-02-16 00:00:00	2023-02-16 23:59:59
1	2373	2023-02-16 14:00:00	2023-02-16 15:00:00
1	2373	2023-02-17 00:00:00	2023-02-17 12:00:00

Note: to_date('16.02.23 14:00', 'dd.mm.yyyy HH24:MI') will give you the year 0023 and not 2023. If you want 2023 then use a 4-digit year or the format model RR or YY.

fiddle