If you do not want to amend your table with extra information, this is how you do it: Suppose you have a table create table events ( time timestamp, object int refers objects(id), -- The thing that had its ignition turned on or off at this time ignition boolean, comment varchar ); You can then do select distinct on (e1.time, e1.object, e1.comment) e1.time as start, e2.time as end, e2.time - e1.time as duration, e1.object, e1.comment from events as e1, events as e2 where e1.object = e2.object and e1.time < e2.time order by e1.time, e1.object, e1.comment, e2.time asc; The trick here is to sort by e2.time in ascending order, and to not include e2.time (or any of e2:s fields) in the distinct condition. This causes all rows where the values from e1 are the same to be considered duplicates, and only the first one of them included in the result - which is the one with the lowest e2.time value, since we did sort on that key in ascending order. Note that this trick works since order by is processed before the distinct clause. This is specifically noted in the PostgreSQL manual. Note that there is a slightly more "intuitive" way of doing this using a subquery with a min() aggregate function, but this is considerably less efficient, especially with larger tables (use "explain" and you'll understund why).
