Re: help with a query

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).