使用php检查mysql中的时间戳冲突

Question

I want to check for a date and time conflict using mysql and php.

My timestamp is formated using php code such as:

$timestamp_start = date("Y-m-d H:i:s", mktime(date("H", strtotime($start_time)), date("i", strtotime($start_time)), 0, $date[0], $date[1], $date[2]));
$timestamp_end = date("Y-m-d H:i:s", mktime(date("H", strtotime($end_time)), date("i", strtotime($end_time)), 0, $date[0], $date[1], $date[2]));

And I do mysql check like this below:

$sql = "SELECT * FROM `$info[table]` WHERE ((`timestamp_start` BETWEEN '$timestamp_start' AND '$timestamp_end') OR (`timestamp_end` BETWEEN '$timestamp_start' AND '$timestamp_end'))";

Which is code I got from here! haha.

Now the issue is that if the room is scheduled for 10/31/2013 from 08:00 AM to 05:00 PM and someone else tries to schedule it for 10/31/2013 from 5:00 PM to 09:00 PM they get a message stating that the room is already in use. Now I didnt mind this at first because than it forced the room to have atleast a 30 minute gap between events, but my bosses said that the room should be able to be scheduled back to back.

How can I modify my sql statement to allow someone to request the room at 05:00 PM if it is booked all the way to 05:00 PM?

This seems to work

$sql = "SELECT * FROM `$info[table]` WHERE `approved`='true' AND (`timestamp_start` < '$timestamp_end' AND `timestamp_end` > '$timestamp_start')";

Answer 1

BETWEEN is inclusive operation, e.g.

 foo BETWEEN bar AND baz

is the equivalent of

 (foo >= bar) AND (foo <= baz)

Since you want to allow "overlap" on the cutoff times, you'll have to ditch the `BETWEEN operation, and go for the more tedious-to-write-out

 (foo >= bar) AND (foo < baz)
                       ^--- non-inclusive