Checking for schedule conflicts
Given the tables defined below, what's the easiest way to check for schedule
conflicts?
So far, the only way I've come up with is to create a huge, multi-dimensional
array in PHP, with a data element for every minute of all time taken up by
all events, and then check for any of these minutes to be set as I go through
all the records. (ugh!)
But, how could I do this in the database?
But I'd like to see something like
"select count(*) FROM events, sched
WHERE sched.date=$date
AND events.id=sched.events_id
...
GROUP BY date, start<finish and finish<start
HAVING count(*) >1 "
And here's where I get stumped. You can't group by start or end because we
need to check if they OVERLAP any other records on the same date.
Ideas?
// Sometimes, recurring events
create table events (
id serial not null primary key,
title varchar
);
// date=YYYYMMDD, start/end: HH:MM (24-hour)
create table sched (
events_id integer not null references events(id),
date integer not null,
start integer not null,
end integer not null
);
insert into events (title)
VALUES ('Tuesday Pickup');
insert into sched(events_id, date, start, end)
VALUES (1, 20050308, 0900, 1300);
insert into sched (events_id, date, start, end)
VALUES (1, 20050315, 0900, 1300);
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978
On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote:
Given the tables defined below, what's the easiest way to check for schedule
conflicts?So far, the only way I've come up with is to create a huge, multi-dimensional
array in PHP, with a data element for every minute of all time taken up by
all events, and then check for any of these minutes to be set as I go through
all the records. (ugh!)But, how could I do this in the database?
But I'd like to see something like
"select count(*) FROM events, sched
WHERE sched.date=$date
AND events.id=sched.events_id
...
GROUP BY date, start<finish and finish<start
HAVING count(*) >1 "And here's where I get stumped. You can't group by start or end because we
need to check if they OVERLAP any other records on the same date.Ideas?
use the OVERLAPS operator ?
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html
gnari
Ragnar Hafsta� wrote:
On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote:
Given the tables defined below, what's the easiest way to check for schedule
conflicts?So far, the only way I've come up with is to create a huge, multi-dimensional
array in PHP, with a data element for every minute of all time taken up by
all events, and then check for any of these minutes to be set as I go through
all the records. (ugh!)But, how could I do this in the database?
But I'd like to see something like
"select count(*) FROM events, sched
WHERE sched.date=$date
AND events.id=sched.events_id
...
GROUP BY date, start<finish and finish<start
HAVING count(*) >1 "And here's where I get stumped. You can't group by start or end because we
need to check if they OVERLAP any other records on the same date.Ideas?
use the OVERLAPS operator ?
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.htmlgnari
The idea is to join table with itself so you can
compare different records, something like:
select * from sched a, sched b /* join with itself */
where (a.start between b.start and b.end /* filter out overlapping */
or a.end between b.start and b.end)
and a.id != b.id /* event overlaps iself - leave that out */
or insted of 'between' use the OVERLAPS operator Ragnar mentioned when
dealing with date types.
Andre
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Benjamin Smith wrote:
Given the tables defined below, what's the easiest way to check for schedule
conflicts?
Assuming you don't count more than once things with the same id, start, and finish:
SELECT COUNT(DISTINCT (s1.event_id||s1.start||s1.finish))
FROM sched s1, sched s2
WHERE s1.date = s2.date AND s1.start >= s2.start
AND s2.finish <= s2.finish AND NOT s1.ctid = s2.ctid
Add "AND s1.date = $date" as needed.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200503140639
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCNXgTvJuQZxSWSsgRAlBmAKC7hj8XzHzS7srqfgdSGOZiCfvtDQCfWM22
VXMkQB7IzEdTKjqpcmWVdaM=
=hFiy
-----END PGP SIGNATURE-----