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.html
>
> gnari
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
