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

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



I have a table of events with a created_at timestamp. I want to divide

them into groups of events that are N seconds apart, specifically 130

seconds. Then for each group, I just need to know the lowest timestamp and

the highest timestamp.

Here's some sample data (ignore the formatting of the timestamp, it's a

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 what I would like to get as a 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 and searched every possible way of phrasing that question and

experimented for some time, but I can't figure it out. I can already do

this in Ruby, I'm just trying to figure out if it's possible to move this

to the database level. If you're curious or it's easier to visualize,

here's what it looks like in Ruby:

groups = SortedSet[*events].divide { |a,b| (a.created_at -

b.created_at).abs <= 130 }

groups.map do |group|

{ started_at: group.to_a.first.created_at, ended_at:

group.to_a.last.created_at }

end

Does anyone know how to do this in SQL, specifically PostgreSQL?