CODEWITHSUNDEEP
postgresql
Alophind
Alophind

Reputation: 862

How to get count of timestamps which has interval bigger than xx seconds between next row in PostgresSQL

I have table with 3 columns (postgres 9.6) : serial , timestamp , clock_name Usually there is 1 second different between each row but sometimes the interval is bigger.

I'm trying to get the number of occasions that the timestamp interval between 2 rows was bigger than 10 seconds (lets say I limit this to 1000 rows)

I would like to do this in one query (probably select from select) but I have no idea how to write such a query , my sql knowladge is very basic.

Any help will be appreciated

Upvotes: 0

Views: 305

Answers (1)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7541

You can use window functions to retrieve the next record record given the current record.

Using the ORDER BY on the function to ensure things are in time stamp order and using PARTITION to keep the clocks separate you can find for each row the row that follows it.

WITH links AS
(
SELECT
  id, ts, clock, LEAD(ts) OVER (PARTITION BY clock ORDER BY ts) AS next_ts
FROM myTable
)
SELECT * FROM links
WHERE
EXTRACT(EPOCH FROM (next_ts - ts)) > 10

You can then just compare the time stamps.

Window functions https://www.postgresql.org/docs/current/static/functions-window.html

Or if you prefer to use derived tables instead of WITH clause.

SELECT * FROM (
  SELECT
    id, ts, clock, LEAD(ts) OVER (PARTITION BY clock ORDER BY ts) AS next_ts
  FROM myTable
) links
WHERE
EXTRACT(EPOCH FROM (next_ts - ts)) > 10

Upvotes: 1

Related Questions