Find a gap > 10min and select begin/end dates in timestamp logs

Lets say I have a table with logs. Two columns: one with user name and second with record timestamp. Records appear in table every 10 minutes for each user IF this user was active.

From such table I need to display data:

• user
• begin (begin of the log sequence)
• end (end of the log sequence )

"Sequence" means that records for the selected user appear every 10 minutes, so every gap > 10min ends current the sequence and the next row for the user starts the next sequence. If there is only one record then I need to display this record as begin and add 10 minutes to begin timestamp to set end.

So the result there should be like this:

How can I do this?

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching:

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
       )

Which, for the sample data:

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;

Outputs:

fiddle