I have an Oracle table with time stamps and I need to check on all rows where the current row is bigger the the previous row by less than a minute and state the start and end time and if its bigger than a minute I need to start a new group as in the example below. (The table is ordered in ASC time

I have the table

I need to pull

CodePudding user response：

You can use:

SELECT id,
       grp,
       MIN(time) AS start_time,
       MAX(time) AS end_time
FROM   (
  SELECT id,
         time,
         SUM(grp_change) OVER (PARTITION BY id ORDER BY time) AS grp
  FROM   (
    SELECT t.*,
           CASE
           WHEN time - LAG(time) OVER (PARTITION BY id ORDER BY time) <= INTERVAL '1' MINUTE
           THEN 0
           ELSE 1
           END AS grp_change
    FROM   table_name t
  )
)
GROUP BY id, grp;

Which, for the sample data:

CREATE TABLE table_name (ID, TIME) AS
SELECT 1, TIMESTAMP '2022-06-14 11:33:03' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2022-06-14 11:34:01' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2022-06-14 11:34:40' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2022-06-14 11:35:59' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2022-06-14 11:38:00' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2022-06-14 11:38:50' FROM DUAL;

Outputs:

ID	GRP	START_TIME	END_TIME
1	2	2022-06-14 11:35:59.000000000	2022-06-14 11:35:59.000000000
1	3	2022-06-14 11:38:00.000000000	2022-06-14 11:38:50.000000000
1	1	2022-06-14 11:33:03.000000000	2022-06-14 11:34:40.000000000

db<>fiddle here