ruby - How can I group by the difference of a column between rows in SQL? -

i have table of events created_at timestamp. want divide them groups of events n seconds apart, 130 seconds. each group, need know lowest timestamp , highest timestamp.

here's sample data (ignore formatting of timestamp, it's datetime field):

 ------------------------ | id | created_at      | ------------------------ | 1  | 2013-1-20-08:00 | | 2  | 2013-1-20-08:01 | | 3  | 2013-1-20-08:05 | | 4  | 2013-1-20-08:07 | | 5  | 2013-1-20-08:09 | | 6  | 2013-1-20-08:12 | | 7  | 2013-1-20-08:20 | ------------------------ 

and result is:

 ------------------------------------- | started_at      | ended_at        | ------------------------------------- | 2013-1-20-08:00 | 2013-1-20-08:01 | | 2013-1-20-08:05 | 2013-1-20-08:09 | | 2013-1-20-08:12 | 2013-1-20-08:12 | | 2013-1-20-08:20 | 2013-1-20-08:20 | ------------------------------------- 

i've googled , searched every possible way of phrasing question , experimented time, can't figure out. can in ruby, i'm trying figure out if it's possible move database level. if you're curious or it's easier visualize, here's looks in ruby:

groups = sortedset[*events].divide { |a,b| (a.created_at - b.created_at).abs <= 130 } groups.map |group|   { started_at: group.to_a.first.created_at, ended_at: group.to_a.last.created_at } end 

does know how in sql, postgresql?

i think want start each new grouping when difference previous greater 130 seconds. can lag , date arithmetic determine grouping starts. cumulative sum grouping:

select grouping, min(created_at), max(created_at) (select t.*, sum(groupstartflag) on (order created_at) grouping       (select t.*,                    lag(created_at) on (order created_at) prevca,                    (case when extract(epoch created_at - lag(created_at) on (order created_at)) < 130                          0 else 1                     end) groupstartflag             t            ) t      ) t group grouping; 

the final step aggregate "grouping" identifier earliest , latest dates.