Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;


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


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


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

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