How to group rows by timestamp?

I have a table like

id         created_at
=========  =========
1          1585461059805
2          1585461062502
3          1585461073123
4          1585461081885
...

The second column is a timestamp. I would like to combine sequences of rows without 5000 ms gap.

Is it possible to thin out the result of the SELECT query?

Answer

You may use LAG for this purpose:

WITH cte AS (
    SELECT *, LAG(created_at, 1, created_at - 5001)
                  OVER (ORDER BY id) lag_created_at
    FROM yourTable
)

SELECT id, created_at
FROM cte
WHERE created_at - lag_created_at > 5000;

Note that I use a default lag value of created_at - 5001 to cover the edge case of the very first record. This first record has no lag, but we still want to include it.