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';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
WITH EVENT_TRIGGER (CAPTURE_DT, CTRL_DT, INST ) AS (
  SELECT TIMESTAMP '2023-02-17 19:21:30.612814', DATE '2023-02-18', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-16 19:18:16.045126', DATE '2023-02-17', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-14 18:58:40.927273', DATE '2023-02-15', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-13 21:43:38.832417', DATE '2023-02-14', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-12 18:30:40.595363', DATE '2023-02-13', 'AAA' FROM DUAL
),
calendar (day) AS (
  SELECT min_dt + LEVEL - 1 AS day
  FROM   (
    SELECT MIN(TRUNC(capture_dt)) AS min_dt,
           MAX(TRUNC(capture_dt)) AS max_dt
    FROM   event_trigger
  )
  CONNECT BY min_dt + LEVEL - 1 <= max_dt
)
SELECT LAG(e.capture_dt) OVER (PARTITION BY e.inst ORDER BY c.day)
         AS STARTTIME,
       e.CAPTURE_DT AS ENDTIME,
       c.day AS BS_DATE
FROM   calendar c
       LEFT OUTER JOIN EVENT_TRIGGER e
       PARTITION BY (e.inst)
       ON (
           c.day <= e.capture_dt
       AND e.capture_dt < c.day + 1
       )
WHERE  e.inst = 'AAA'
ORDER BY c.day DESC;


STARTTIME
ENDTIME
BS_DATE


2023-02-16 19:18:16.045126000
2023-02-17 19:21:30.612814000
2023-02-17 00:00:00

null
2023-02-16 19:18:16.045126000
2023-02-16 00:00:00

2023-02-14 18:58:40.927273000
null
2023-02-15 00:00:00

2023-02-13 21:43:38.832417000
2023-02-14 18:58:40.927273000
2023-02-14 00:00:00

2023-02-12 18:30:40.595363000
2023-02-13 21:43:38.832417000
2023-02-13 00:00:00

null
2023-02-12 18:30:40.595363000
2023-02-12 00:00:00

STARTTIME	ENDTIME	BS_DATE
2023-02-16 19:18:16.045126000	2023-02-17 19:21:30.612814000	2023-02-17 00:00:00
null	2023-02-16 19:18:16.045126000	2023-02-16 00:00:00
2023-02-14 18:58:40.927273000	null	2023-02-15 00:00:00
2023-02-13 21:43:38.832417000	2023-02-14 18:58:40.927273000	2023-02-14 00:00:00
2023-02-12 18:30:40.595363000	2023-02-13 21:43:38.832417000	2023-02-13 00:00:00
null	2023-02-12 18:30:40.595363000	2023-02-12 00:00:00