From: Richard Huxton <dev@archonet.com>
To: Marcin Stępnicki <mstepnicki@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Finding gaps in scheduled events
Date: Tue, 12 Dec 2006 20:13:25 +0000
Message-ID: <457F0D65.8060303@archonet.com> (raw)
In-Reply-To: <pan.2006.12.12.09.08.15.137773@gmail.com>
References: <pan.2006.12.12.09.08.15.137773@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, mstepnicki@gmail.com
Subject: Re: Finding gaps in scheduled events
In-Reply-To: <457F0D65.8060303@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox