SQL command between two datetimes for table reservation

Question

I am developing at the moment the reservation system for a restaurant that is always full and if anyone wants to come must make your reservation.

This means that the host enters arrival and departure, so the exact interval in which the host wants to be in the restaurant.

So if that customer A has a reservation 8:00 p.m. to 10:00 p.m. and customer arrives B, who wants to come 9:00 p.m. to 11:00 p.m. and wants to book the same table as the customer A system will not let go, because the times overlap .

$GuetComming = '2014-01-21 12:30:00'
$GuestDepature = '2014-01-21 16:30:00'
$i= '10'

SQL query:

SELECT * FROM `booking`
    where (Table='$i' AND Comming BETWEEN '$GuestComming' AND '$GuestDepature')
    or (Table='$i' AND Depature BETWEEN '$GuestComming' AND '$GuestDepature')
    or (Table='$i' AND Comming <= '$GuestComming' AND Depature >='$GuestDepature')
    or (Table='$i' AND Comming BETWEEN '$GuestComming' AND '$GuestDepature')

I am currently command that works, but I believe it would be much easier.

This is how it works, but unfortunately it happens sometimes that a table is available when it is reserved.

Therefore, seeking a better solution, because the restaurant is approximately 100 tables and I believe that sometimes it can happen that the server does not manage to find out whether a table is reserved.

What should this query return? Is it a list of tables not available? — athabaska, Jan 21 '14 at 10:17
SQL should return true or false, so that the table is full or not. Accordingly, the print table. If it is false it is possible to click on it and vice versa when the table is true (full) and then then turns red — user3218484, Jan 21 '14 at 10:25
See if the accepted answer helps: http://stackoverflow.com/questions/143552/comparing-date-ranges — Salman A, Jan 21 '14 at 10:31
So you checking if specified table is taken or not. What is 'odchodHosta' field? — athabaska, Jan 21 '14 at 10:33
This means that the customer enters the arrival and departure of a SQL command checks whether a record exists in the database that has the same desk as entered customer and whether the customer specified times do not overlap with the already created record. — user3218484, Jan 21 '14 at 10:39
I'm sorry I forgot to overwrite 'OdchodZakaznika' is Depature column — user3218484, Jan 21 '14 at 10:39

score 2 · Accepted Answer · edited Jan 21 '14 at 11:01

2

I would reduce it to following:

SELECT * FROM `booking`
WHERE Table='$i' AND NOT(Comming >= '$GuestDepature' OR Depature <= '$GuestComming')

Query result should be empty if table is reserded. Not sure how 'odchodHosta' field works.

//Update SQL- Added NOT

edited Jan 21 '14 at 11:01

user3218484

15
4

answered Jan 21 '14 at 10:38

athabaska

455
3
22

Yes than this one is the good answer, more information you can find more about this problem at http://en.wikipedia.org/wiki/Allen%27s_Interval_Algebra – Nelly Jan 21 '14 at 10:41
I'm sorry I forgot to overwrite 'OdchodZakaznika' is Depature column – user3218484 Jan 21 '14 at 10:43

SQL command between two datetimes for table reservation

1 Answers1

Linked