How to group timestamps into islands (based on arbitrary gap)?

by 
SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS grp
FROM  (
   SELECT done
        , lag(done) OVER (ORDER BY done) <= done - interval '2 min' AS step
   FROM   tbl
   ) sub
ORDER  BY done;

The subquery sub returns step = true if the previous row is at least 2 min away – sorted by the timestamp column done itself in this case.

The outer query adds a rolling count of steps, effectively the group number (grp) – combining the aggregate FILTER clause with another window function.

fiddle

Related:

About the aggregate FILTER clause:

More Related Contents:

  1. GROUP BY and aggregate sequential numeric values
  2. PostgreSQL unnest() with element number
  3. Dynamic alternative to pivot with CASE and GROUP BY
  4. PostgreSQL: running count of rows for a query ‘by minute’
  5. Calculating Cumulative Sum in PostgreSQL
  6. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  7. Get the distinct sum of a joined table column
  8. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  9. Window Functions or Common Table Expressions: count previous rows within range
  10. Postgres window function and group by exception
  11. Referencing current row in FILTER clause of window function
  12. How to use a ring data structure in window functions
  13. Conditional lead/lag function PostgreSQL?
  14. Computed / calculated / virtual / derived columns in PostgreSQL
  15. Insert data in 3 tables at a time using Postgres
  16. Spark SQL window function with complex condition
  17. PostgreSQL: Which Datatype should be used for Currency?
  18. PostgreSQL sequence based on another column
  19. Finding similar strings with PostgreSQL quickly
  20. Sort NULL values to the end of a table
  21. Oracle “Partition By” Keyword
  22. Select (retrieve) all records from multiple schemas using Postgres
  23. Optimize groupwise maximum query
  24. Use email address as primary key?
  25. Creating temporary tables in SQL
  26. SQL query to find record with ID not in another table
  27. Get execution time of PostgreSQL query
  28. Postgresql tables exists, but getting “relation does not exist” when querying
  29. Postgresql query between date ranges
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment