CODEWITHSUNDEEP
sqlpostgresql
henrycase
henrycase

Reputation: 23

Postgres select if another event exists before and after a time range

I have a table like this:

enter image description here

I need a select the following records:

To create a test table: 

     CREATE TABLE test(
       time TIMESTAMP,
       name CHAR(10),
       category CHAR(50)
    );

INSERT INTO test (time, name, category)
    VALUES ('2019-02-25 18:30:10', 'john', 'A'),
           ('2019-02-25 18:30:15', 'john', 'B'),
           ('2019-02-25 19:00:00', 'phil', 'A'),
           ('2019-02-25 20:00:00', 'tim', 'A'),
           ('2019-02-25 21:00:00', 'tim', 'B'),
           ('2019-02-25 21:00:00', 'frank', 'B');

So from the above, this is the desired output:

enter image description here

Upvotes: 2

Views: 341

Answers (2)

Andomar
Andomar

Reputation: 238048

You can use an exists subquery to determine if there is an A row within 20 seconds:

select  *
from    test t1
where   category = 'A'
        or exists
        (
        select  *
        from    test t2
        where   t2.category = 'A'
                and t2.name = t1.name
                and abs(extract(epoch from t2.time - t1.time)) < 20
        )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use exists. But you can also use window functions:

select t.*
from (select t.*,
             max(t.time) filter (t.category = 'A') over (partition by name order by time) as prev_a,
             min(t.time) filter (t.category = 'A') over (partition by name order by time desc) as next_a
      from test t
     ) t
where category = 'A' or
      (category = 'B' and
       (prev_a > time - interval '20 second' or
        next_a < time + interval '20 second'
       )
      );

Upvotes: 0

Related Questions