Find overlapping dates

[ZwoksKorbi]

Hi Guys,

 

currently i'm programming a web-based Calendar System to manage the assignment of seminar rooms.

If someone is assigning a new appointment he can choose the start and the end-date/time.

I need to check if the choosen room is available at the given dates.

I've tried to build the query on my own but i faild. :-\

So may someone can help me?

 

Things i have to check:

           NS        NE        
           |        |
    OS--OE |        |               //OK
        OS-|-OE     |               //Not OK
           | OS--OE |               //Not OK
           |     OS-|-OE            //Not OK
           |        | OS--OE        //OK
        OS-|--------|-OE            //Not OK

 

Here's the structure of my appointments table

CREATE TABLE IF NOT EXISTS `appointments` (
  `ID` int(11) NOT NULL auto_increment,
  `room` smallint(6) NOT NULL,
  `creator` varchar(60) collate utf8_bin NOT NULL,
  `subject` varchar(128) collate utf8_bin NOT NULL,
  `description` text collate utf8_bin NOT NULL,
  `Start` datetime NOT NULL,
  `End` datetime NOT NULL,
  `wholeday` tinyint(1) NOT NULL,
  `moreday` tinyint(1) NOT NULL,
  `isPublic` tinyint(1) NOT NULL default '1' COMMENT 'not implemented yet',
  PRIMARY KEY  (`ID`),
  KEY `Start` (`Start`,`End`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8 ;

 

I'm looking forward hearing from you.

 

Korbi

[ZwoksKorbi]

NS = New Start

NE = New End

OS = Old Start

OE = Old End

[fenway]

What queries have you tried?

[ZwoksKorbi]

The latest try:

SELECT 
COUNT(*) /* if >0 => not Ok*/
FROM 
`appointments` 
WHERE
(	
(
(	/*NewStart between old-start and -end */
	'$neuStart' >= UNIX_TIMESTAMP(`Start`)
	AND
	'$neuStart' < UNIX_TIMESTAMP(`End`)
) 
OR
(	/* NewEnd between old-start and -end */
	'$neuEnd' <= UNIX_TIMESTAMP(`End`)
	AND
	'$neuEnd' > UNIX_TIMESTAMP(`Start`)
)
OR	
(	/* New Appointment is completely between old-start and -end*/
	'$neuStart' <= UNIX_TIMESTAMP(`Start`)
	AND
	'$neuEnd' >= UNIX_TIMESTAMP(`End`)
)
OR	
(	/* old appointment is completely between new-start and -end*/
	'$neuStart' >= UNIX_TIMESTAMP(`Start`)
	AND
	'$neuEnd' <= UNIX_TIMESTAMP(`End`)
)

)

AND
`room` = '$room' /*search appointments only in choosen room*/
);

[fenway]

Can't you simplify this to check to the following

 

1) OE < NS

  AND OR

2) OS > NE

 

Assuming you can rely of end > start...

[Barand]

I think that should be an OR

[fenway]

I think that should be an OR

I meant check both, not database AND.  Sorry about the confusion.

 

[Barand]

If I were checking I'd look for any bookings that do overlap my required slot so my query would be the other way round

 

SELECT COUNT(*) 
FROM bookings
WHERE (`Start` < '$ne') AND (`End` > $ns)
AND room = '$room'

 

If count is 0, book it.