CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Lauri
Lauri

Reputation: 4800

Filtering time series data based on previous result row values in PostgreSQL

I have data in table that is time series. The table has timestamp column which is of type timestamp. I need to filter this table so that a row is returned by a query only if its timestamp greater than the previous result row's timestamp plus a configured interval.

If the configured interval is 3 seconds I would like the rows marked with an arrow in the following data to be returned:

2015-01-20T12:00:00 <-
2015-01-20T12:00:01
2015-01-20T12:00:02
2015-01-20T12:00:03 <-
2015-01-20T12:00:06 <-
2015-01-20T12:00:10 <-
2015-01-20T12:00:12
2015-01-20T12:00:13 <-
2015-01-20T12:00:14
2015-01-20T12:00:15
2015-01-20T12:00:16 <-

I have tried to use window functions to get the correct results. But the problem is that I can only group the results in 3 second intervals and in that solution rows returned from 2015-01-20T12:00:12 on would be different.

I'm not even sure if this can be done as the rows to be returned depend on the previous results. So my question is if this can be done with Postgres 9.3 in a n efficient way.

Upvotes: 0

Views: 386

Answers (1)

pozs
pozs

Reputation: 36244

I'm afraid, this cannot be solved with window functions, because rows (have to be returned) depend on the previous results, as you noted.

But this is exactly what a recursive CTE does:

with recursive r as (
  (select   *
   from     t
   order by ts
   limit    1)
  union all
  (select   t.*
   from     t
   join     r on t.ts >= r.ts + interval '3 sec'
   order by t.ts
   limit    1)
)
select *
from   r;

SQLFiddle

Note however, that this solution will search for the results row-by-row, so it won't be effective with large result-sets.

Upvotes: 2

Related Questions