Message-ID: <457F0D65.8060303@archonet.com>
Date: Tue, 12 Dec 2006 20:13:25 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Marcin_St=EApnicki?= <mstepnicki@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Finding gaps in scheduled events
References: <pan.2006.12.12.09.08.15.137773@gmail.com>
In-Reply-To: <pan.2006.12.12.09.08.15.137773@gmail.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable

Marcin St=EApnicki wrote:
>=20
> 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 progr=
ams
> (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=20
database for information based on what *isn't* stored in it. That is=20
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=20
time and compare finish/start times in order. You can do something=20
similar with PG and write a plpgsql function that returns a setof=20
(start,finish,length) for gaps.

If you have a lot of events and you need to find gaps quite often it=20
might be easier to keep a separate table to track them. Triggers on the=20
events table would keep the gaps table up to date. If events can be=20
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=20
"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=20
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=20
probably run over the table twice (via indexes).

HTH
--=20
   Richard Huxton
   Archonet Ltd