FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

CREATE TABLE table_name (username, datetime) AS
SELECT 'user1', TIMESTAMP '2024-08-07 20:10:00.000000' FROM DUAL UNION ALL
SELECT 'user2', TIMESTAMP '2024-08-07 20:10:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 20:10:00.000000' FROM DUAL UNION ALL
SELECT 'user1', TIMESTAMP '2024-08-07 20:20:00.000000' FROM DUAL UNION ALL
SELECT 'user2', TIMESTAMP '2024-08-07 20:20:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 20:20:00.000000' FROM DUAL UNION ALL
SELECT 'user1', TIMESTAMP '2024-08-07 20:30:00.000000' FROM DUAL UNION ALL
SELECT 'user2', TIMESTAMP '2024-08-07 20:30:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 20:30:00.000000' FROM DUAL UNION ALL
SELECT 'user2', TIMESTAMP '2024-08-07 20:40:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 20:40:00.000000' FROM DUAL UNION ALL
SELECT 'user1', TIMESTAMP '2024-08-07 20:50:00.000000' FROM DUAL UNION ALL
SELECT 'user2', TIMESTAMP '2024-08-07 20:50:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 20:50:00.000000' FROM DUAL UNION ALL
SELECT 'user1', TIMESTAMP '2024-08-07 21:00:00.000000' FROM DUAL UNION ALL
SELECT 'user1', TIMESTAMP '2024-08-07 21:10:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 22:00:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 22:20:00.000000' FROM DUAL UNION ALL
SELECT 'user3', TIMESTAMP '2024-08-07 22:30:00.000000' FROM DUAL;

SELECT username,
       begin_time,
       CASE
       WHEN begin_time = end_time
       THEN end_time + INTERVAL '10' MINUTE
       ELSE end_time
       END AS end_time
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY username
         ORDER BY datetime
         MEASURES
           FIRST(datetime) AS begin_time,
           LAST(datetime) AS end_time
         PATTERN ( first_time consecutive_times* )
         DEFINE
           consecutive_times AS datetime <= PREV(datetime) + INTERVAL '10' MINUTE
       )

USERN	BEGIN_TIME	END_TIME
user1	2024-08-07 20:10:00.000000000	2024-08-07 20:30:00.000000000
user1	2024-08-07 20:50:00.000000000	2024-08-07 21:10:00.000000000
user2	2024-08-07 20:10:00.000000000	2024-08-07 20:50:00.000000000
user3	2024-08-07 20:10:00.000000000	2024-08-07 20:50:00.000000000
user3	2024-08-07 22:00:00.000000000	2024-08-07 22:10:00.000000000
user3	2024-08-07 22:20:00.000000000	2024-08-07 22:30:00.000000000