CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Hello lad
Hello lad

Reputation: 18790

examine if one time series column of table has two adjacent time points which have interval larger than certain length

I am dealing with data preprocessing on a table containing time series column

toy example Table A

    timestamp   value

    12:30:24    1
    12:32:21    3
    12:33:21    4

timestamp is ordered and always go incrementally

Is that possible to define an function or something else to return "True expression" when table has two adjacent time points which have interval larger than certain length and return "False" otherwise?

I am using postgresql, thank you

Upvotes: 0

Views: 55

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

SQL Fiddle

select bool_or(bigger_than) as bigger_than
from (
    select 
        time - lag(time) over (order by time) 
        > 
        interval '1 minute' as bigger_than
    from table_a
) s;
 bigger_than 
-------------
 t

bool_or will stop searching as soon as it finds the first true value.

http://www.postgresql.org/docs/current/static/functions-aggregate.html

Your sample data shows a time value. But it works the same for a timestamp

Upvotes: 1

user330315
user330315

Reputation:

Something like this:

select count(*) > 0
from (
  select timestamp, 
         lag(timestamp) over (order by value) as prev_ts
  from table_a
) t
where timestamp - prev_ts < interval '1' minute;

It calculates the difference between a timestamp and it's "previous" timestamp. The order of the timestamps is defined by the value column. The outer query then counts the number of rows where the difference is smaller than 1 minute.

lag() is called a window functions. More details on those can be found in the manual:
http://www.postgresql.org/docs/current/static/tutorial-window.html

Upvotes: 0

Related Questions