sqlpostgresqlplpgsql

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.


Solution

  • 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