Search code examples
sqloraclegaps-and-islands

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.

user timestamp
user1 07/08/2024 20:10:00,000000
user2 07/08/2024 20:10:00,000000
user3 07/08/2024 20:10:00,000000
user1 07/08/2024 20:20:00,000000
user2 07/08/2024 20:20:00,000000
user3 07/08/2024 20:20:00,000000
user1 07/08/2024 20:30:00,000000
user2 07/08/2024 20:30:00,000000
user3 07/08/2024 20:30:00,000000
user2 07/08/2024 20:40:00,000000
user3 07/08/2024 20:40:00,000000
user1 07/08/2024 20:50:00,000000
user2 07/08/2024 20:50:00,000000
user3 07/08/2024 20:50:00,000000
user1 07/08/2024 21:00:00,000000
user1 07/08/2024 21:10:00,000000
user3 07/08/2024 22:00:00,000000
user3 07/08/2024 22:20:00,000000
user3 07/08/2024 22:30:00,000000

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:

user begin end
user1 07/08/2024 20:10:00,000000 07/08/2024 20:30:00,000000
user2 07/08/2024 20:10:00,000000 07/08/2024 20:50:00,000000
user3 07/08/2024 20:10:00,000000 07/08/2024 20:50:00,000000
user1 07/08/2024 20:50:00,000000 07/08/2024 21:10:00,000000
user3 07/08/2024 22:00:00,000000 07/08/2024 22:10:00,000000
user3 07/08/2024 22:20:00,000000 07/08/2024 22:30:00,000000

How can I do this?

Solution

  • 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:

    USERNAME 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

    fiddle