Marcin Stępnicki wrote:
>
> start | finish | type_id
> ------+--------+--------
> 8:30 | 8:45 | 1 -> type A
> 9:30 | 10:00 | 2 -> type B
> I have to deal with the existing schema, but if it can be done in a
> better way please let me know so I could avoid mistakes in my own programs
> (although I think it's quite flexible and I like the concept).
The reason you're finding it difficult is that you're asking the
database for information based on what *isn't* stored in it. That is
you're asking it for all the gaps in your event data.
Now, if you were doing it by hand you'd sort the events according to
time and compare finish/start times in order. You can do something
similar with PG and write a plpgsql function that returns a setof
(start,finish,length) for gaps.
If you have a lot of events and you need to find gaps quite often it
might be easier to keep a separate table to track them. Triggers on the
events table would keep the gaps table up to date. If events can be
deleted/moved you'll want to consider how to merge adjacent gaps.
If you don't like either of those, you'll need to figure out what the
"next" and "previous" events are for each event in your table. That will
need to be a sub-query with something like:
SELECT a.start, a.finish, ( SELECT start FROM test_events WHERE start>a.finish ORDER BY start
LIMIT 1 ) AS next_start
FROM test_events a
ORDER BY start;
Note the subquery is in the SELECT clause and this query-plan will
probably run over the table twice (via indexes).
HTH
-- Richard Huxton Archonet Ltd