Finding gaps in scheduled events
От | Marcin Stępnicki |
---|---|
Тема | Finding gaps in scheduled events |
Дата | |
Msg-id | pan.2006.12.12.09.08.15.137773@gmail.com обсуждение исходный текст |
Ответы |
Re: Finding gaps in scheduled events
Re: Finding gaps in scheduled events |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: