SQL Check if available between times

Question

I'm trying to code a system that let's user book items. Items can't be booked twice. So if an item is unavailable i would like to know that by using SQL.

I've got a database with the following fields id,item, start (datetime), end (datetime)

A user will try to book an item from "01-01-2016 09:00" till "01-01-2016-22:00". But there is already a row where start is "01-01-2016 11:00" and end is "01-01-2016 16:00". So.. The item isn't available.

How can i do that in SQL?

Now i am using this:

SELECT * FROM `items` WHERE (`start` between '$start' and '$end') AND (`item` = $id)

Then i look if the number of rows is higher then 0. If so, the item is unavailable. This works.. but not when the start time is BEFORE the new start time.

I'm hoping that my explanation is clear... Having a hard time dealing with this issue because i can't get the facts straight.

Maybe this will help you: http://stackoverflow.com/questions/17407481/check-if-a-time-is-between-two-times-time-datatype — Michał Perłakowski, Dec 28 '15 at 16:41

Bfcm · Accepted Answer · 2015-12-28T16:58:45.977

0

It is okey actually that start could be before the new start time. But you have also check if the end is also in the between interval. Also for the comment case, you have to check if there is smaller interval. Expand your query following way:

SELECT * FROM `items` WHERE ((`start` between '$start' and '$end') OR (`end` between '$start' and '$end') OR (`start` >= '$start' and `end` <=  '$end')) AND (`item` = $id)

edited Dec 28 '15 at 16:58

answered Dec 28 '15 at 16:41

Bfcm

2,686
2
27
34

This will not work when the row is start "20:00:00" and end is "23:00:00" and $start is "21:00:00" and $end is "22:00:00". – PowerGuitarist Dec 28 '15 at 16:50
Sorry. You need one more condition. See updated query. – Bfcm Dec 28 '15 at 16:58
Thx! First i thought your edit didn't work but i made a typo! THX! – PowerGuitarist Dec 28 '15 at 17:08

SQL Check if available between times

1 Answers1