• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

how do I write a mysql query to check if is an item is available during the time period requeste

Hey there experts :) ,

I am building a system for booking a room, and I now need to work out how to tell if the room is already booked during the a requested time period

In a booking table on my mysql database I have two datetime fields called "start" and "end" which contain the begining and ending times of each booking.

How do I write a query that compares a requested "start" and "end" with those already in the database and determins if the time period is available or not?


e.g.

If a user reqest a booking to start:  2010/05/12 08:00  and end: 2010/05/12 10:00

I need to be able to compare this to my bookings table and check if the period between 8 and 10 oclock on the 12th april 2010 is already taken
0
Susurrus
Asked:
Susurrus
1 Solution
 
theremonCommented:
Hi there

I think that somethink in the lines of the following query could help. Dates used are your examples.

SELECT * FROM `bookings` where roomid=1 and 
   (
    (('2010-05-12 08:00' between startdate and enddate) or 
    ('2010-05-12 10:00' between startdate and enddate))
   or (startdate between '2010-05-12 08:00' and '2010-05-12 10:00')
   or (enddate between '2010-05-12 08:00' and '2010-05-12 10:00')
   )

Open in new window

0
 
flytox06Commented:
SELECT COUNT(*)
FROM booking
WHERE start <= '2010-05-12 10:00'
AND end >=  '2010-05-12 08:00'

If count = 0, then your room is free.
0
 
theremonCommented:
flytox,

don't want to be rude and seem like I pass judgement on your answer, but it's wrong. The query you posted will cover only one of the possible date combinations. It'll return >0 only if there's an existing booking that starts before the given start date and ends after the given ending date. There are several other cases however. For example, say there's a booking that starts before the given date but also ends before the ending date. Or it starts and ends between the given dates. And so on, so forth.
Your query doesn't cover all that.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
flytox06Commented:
No worries for the judgement, a feedback is always welcome positively. But it does cover all cases, please re-read the corresponding dates in filter.

Note: advantage of this method is that you make a better use of indexes on date.
0
 
theremonCommented:
Ok

I just fell flat on my face :)
This is totally not my day. I completely missed how you had reverted the dates.
My apologies.
0
 
flytox06Commented:
no prob, it's not easy to get at first shot in fact since it's a "NOT" way of thinking :-)
0
 
theremonCommented:
Nah, reading the query makes perfect sense. I guess I'm too tired today and just read the dates as I had them already in my mind - ie. I didn't actually READ what you had typed...
0
 
SusurrusAuthor Commented:
wow, I didn't realise my little question would illicit such attention and discussion :).  Thanks alot guys, I will try it out shortly and tell you how I got on and of course alot those ever important points ;)
0
 
gjanssenmnCommented:
This works for a static time and static date, how about for a static time but a dynamic date using Date()  function?  Say I always want the items between 10:00 am and 11:00 am 'yesterday'?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now