How to group by period of time?

user_15974340 0
Viewed 62

I have a table with messages and I need to find chats where were two or more messages in period of 10 seconds. table

id message_id time
1  1          13:09:00
1  2          13:09:01
1  3          13:09:50
2  1          15:18:00           
2  2          15:20:00
3  1          15:00:00
3  2          15:10:00
3  3          15:10:10

So the result looks like

id
1
3

I can't come up with the idea how to group by a period or maybe it can be done other way?

select id
from t
group by id, ?
having count(message_id) > 1
postgresql sql
1 Answers

You can use a self-join:

with add_ymd(id, mid, dt) as (
   select id, message_id, (date(now())||' '||time)::timestamp from messages
),
tm_counts as (
   select t2.id, max(t2.n) + 1 tm from (
     select t.id, t.mid, sum(case when extract(epoch from t.dt - t1.dt) <= 10 then 1 end) n
     from add_ymd t join add_ymd t1 on t.dt > t1.dt group by t.id, t.mid) t2
    where t2.n is not null group by t2.id 
)
select id from tm_counts where tm > 1

Output:

id
---
1
3
user_73267380
Related