Oracle 21c

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

CREATE TABLE test_tab (
    col_name  VARCHAR2(20),
    log_time  TIMESTAMP(6),
    status    VARCHAR2(20)
);

INSERT INTO test_tab VALUES('Engineering'
                           ,TO_DATE('08-06-22 08:09:16', 'DD-MM-YY HH:MI:SS'), 'UP');
1 rows affected

INSERT INTO test_tab VALUES('Engineering',
                            TO_DATE('09-06-22 08:28:16', 'DD-MM-YY HH:MI:SS'),'UP');
1 rows affected

INSERT INTO test_tab VALUES('Engineering',
                            TO_DATE('09-06-22 08:13:16', 'DD-MM-YY HH:MI:SS'),'DOWN');
1 rows affected

INSERT INTO test_tab VALUES('Commerce',
                            TO_DATE('07-06-22 4:59:16', 'DD-MM-YY HH:MI:SS'),'DOWN');
1 rows affected

INSERT INTO test_tab VALUES('Commerce',
                            TO_DATE('07-06-22 6:34:16', 'DD-MM-YY HH:MI:SS'),'UP');
1 rows affected

INSERT INTO test_tab VALUES('Commerce', 
                            TO_DATE('07-06-22 6:49:16', 'DD-MM-YY HH:MI:SS'),'DOWN');
1 rows affected

INSERT INTO test_tab VALUES('Commerce',
                            TO_DATE('01-06-22 2:15:16', 'DD-MM-YY HH:MI:SS'),'UP');
1 rows affected

INSERT INTO test_tab VALUES('Commerce',
                            TO_DATE('07-06-22 07:04:16', 'DD-MM-YY HH:MI:SS'),'UP');
1 rows affected

SELECT  col_name, log_time AS start_time, status
FROM  test_tab
WHERE status = 'DOWN'


COL_NAME
START_TIME
STATUS


Engineering
09-JUN-22 08.13.16.000000
DOWN

Commerce
07-JUN-22 04.59.16.000000
DOWN

Commerce
07-JUN-22 06.49.16.000000
DOWN


SELECT col_name,log_time AS end_time, status
FROM test_tab
WHERE status = 'UP'


COL_NAME
END_TIME
STATUS


Engineering
08-JUN-22 08.09.16.000000
UP

Engineering
09-JUN-22 08.28.16.000000
UP

Commerce
07-JUN-22 06.34.16.000000
UP

Commerce
01-JUN-22 02.15.16.000000
UP

Commerce
07-JUN-22 07.04.16.000000
UP


WITH a AS(
SELECT  col_name, log_time AS start_time, status
  FROM  test_tab
WHERE status = 'DOWN'),
b AS(
SELECT col_name,log_time AS end_time, status
FROM test_tab
WHERE status = 'UP')
SELECT a.col_name
       , a.start_time
       , a.status
       , b.end_time
       , b.status
FROM a
LEFT JOIN b ON a.col_name = b.col_name


COL_NAME
START_TIME
STATUS
END_TIME
STATUS


Engineering
09-JUN-22 08.13.16.000000
DOWN
08-JUN-22 08.09.16.000000
UP

Engineering
09-JUN-22 08.13.16.000000
DOWN
09-JUN-22 08.28.16.000000
UP

Commerce
07-JUN-22 04.59.16.000000
DOWN
07-JUN-22 06.34.16.000000
UP

Commerce
07-JUN-22 06.49.16.000000
DOWN
07-JUN-22 06.34.16.000000
UP

Commerce
07-JUN-22 04.59.16.000000
DOWN
01-JUN-22 02.15.16.000000
UP

Commerce
07-JUN-22 06.49.16.000000
DOWN
01-JUN-22 02.15.16.000000
UP

Commerce
07-JUN-22 04.59.16.000000
DOWN
07-JUN-22 07.04.16.000000
UP

Commerce
07-JUN-22 06.49.16.000000
DOWN
07-JUN-22 07.04.16.000000
UP


WITH a AS(
SELECT  col_name, log_time AS start_time, status
  FROM  test_tab
WHERE status = 'DOWN'),
b AS(
SELECT col_name,log_time AS end_time, status
FROM test_tab
WHERE status = 'UP')
SELECT a.col_name
       , a.start_time
       , a.status
       , MIN(b.end_time)
       , b.status
FROM a
LEFT JOIN b ON a.col_name = b.col_name
WHERE a.start_time < b.end_time
GROUP BY a.col_name
       , a.start_time
       , a.status
       , b.status ;


COL_NAME
START_TIME
STATUS
MIN(B.END_TIME)
STATUS


Engineering
09-JUN-22 08.13.16.000000
DOWN
09-JUN-22 08.28.16.000000
UP

Commerce
07-JUN-22 04.59.16.000000
DOWN
07-JUN-22 06.34.16.000000
UP

Commerce
07-JUN-22 06.49.16.000000
DOWN
07-JUN-22 07.04.16.000000
UP

COL_NAME	START_TIME	STATUS
Engineering	09-JUN-22 08.13.16.000000	DOWN
Commerce	07-JUN-22 04.59.16.000000	DOWN
Commerce	07-JUN-22 06.49.16.000000	DOWN

COL_NAME	END_TIME	STATUS
Engineering	08-JUN-22 08.09.16.000000	UP
Engineering	09-JUN-22 08.28.16.000000	UP
Commerce	07-JUN-22 06.34.16.000000	UP
Commerce	01-JUN-22 02.15.16.000000	UP
Commerce	07-JUN-22 07.04.16.000000	UP