Checking for schedule conflicts

Benjamin Smith · *nix forums beginner Joined: 12 Mar 2005 Posts: 30

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Ragnar Hafstað · *nix forums beginner Joined: 01 Mar 2005 Posts: 37

On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote:

Andre Maasikas · *nix forums beginner Joined: 14 Mar 2005 Posts: 2

Ragnar Hafstað wrote:

Greg Sabino Mullane · *nix forums beginner Joined: 07 Mar 2005 Posts: 47

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Benjamin Smith wrote:

Google