To: pgsql-sql@postgresql.org
From: =?iso-8859-2?q?Marcin_St=EApnicki?= <mstepnicki@gmail.com>
Subject: Finding gaps in scheduled events
Date: Tue, 12 Dec 2006 10:08:15 +0100
Organization: Linux.PL
Lines: 78
Message-ID: <pan.2006.12.12.09.08.15.137773@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.14.2.91 (As She Crawled Across the Table (Debian GNU/Linux))

Hello.

I've been struggling with this one for over a week, but for some reason my
mind isn't compatibile with the problem - it seems simple, yet I'm unable
to find the proper solution :(.

I have a timeline divided to 15 minute periods:

start |
------+
 8:00 |  
 8:15 |
 8:30 |
 8:45 |
 (...)|
14:45 |

Then, I have two types of events that fit the schedule. Event A takes
15 minutes, event B takes 30 minutes. They're stored in a table like this:

start | finish | type_id
------+--------+--------
8:30  |  8:45  |    1    -> type A
9:30  | 10:00  |    2    -> type B

Now I need to create a query to find hours at which each of the type can
start. So, if it's event A (which take 15 minutes) it can start at:

8:00 (to 8:15)
8:15 (to 8:30)
( 8:30 to 8:45 is already taken )
8:45 (to 9:00)
9:00 (to 9:15)
9:15 (to 9:30)
( 9:30 to 10:00 (9:30-9:45 and 9:45-10:00) is already taken))
10:00 (to 10:15)
(...)

and if it's event B (which takes 30 minutes) it can start at:

8:00 (to 8:30) 
8:45 (to 9:15)
9:00 (to 9:30)
10:00 (to 10:30)
(...)

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

Example tables:

create table test_events ( 
             id serial,
	     start time,
	     finish time,
	     type_id integer );

insert into test_events ( start,finish,type_id ) values('8:30','8:45','1');
insert into test_events ( start,finish,type_id ) values ('9:30','10:00','2');

create table test_timeline as 
       SELECT 
	 ('0:00'::TIME + (my_day.h || ' minutes')::INTERVAL)::TIME as my_hour
       FROM
         generate_series (0,1425,15) AS my_day(h);

I don't paste my tries because they've all failed and I think I miss
something fundamental here.

Thank you very much for your time.

-- 
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
|    So be it in love that harms none   | http://biznes.linux.pl
|   For this is the only commandment.   | http://www.juanperon.info
`---*  JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org