MySQL querying date range

Question

Here's my database (free rooms in a hotel, simplified)

rooms_available:

id date_available room_id ================================ 1 2013-12-19 2 2 2013-12-20 2 3 2013-12-21 2 4 2013-12-22 2 5 2013-12-23 2 6 2013-12-25 3

rooms:

id name minimal_range ================================ 2 Apartment A 5 3 Apartment B 1

I want to query all rooms which are available between 2013-12-20 and 2013-12-22

My query is like:

select * from rooms_available where (date='2013-12-20' OR date='2013-12-21' OR date='2013-12-22')

My questions:

is there a more comfortable way? when the date range will be like 2 weeks, the query will also be very long (which will take much longer for querying)
would it be possible to consider minimum ranges - for example: room_id 2 is only available for at least 5 nights (see table "rooms") -> so above query should return no records

Thanks

I suspect that it's more common to store when the rooms are taken rather than when they're available — Strawberry
– Strawberry, Commented Dec 19, 2013 at 10:45
This question is asked about 100 times on SO, so you might want to check already answered question next time — jeroenvisser101
– jeroenvisser101, Commented Dec 19, 2013 at 10:50

Scott Hunter · Accepted Answer · 2013-12-19 10:45:53Z

1

date >= '2013-12-20' and date <= '2013-12-22'

answered Dec 19, 2013 at 10:45

Scott Hunter

50k12 gold badges64 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

웃웃웃웃웃 · Accepted Answer · 2013-12-19 11:16:56Z

1

SELECT * FROM rooms_available WHERE `date_available` BETWEEN "2013-12-20 " AND "2012-03-31"

edited Dec 19, 2013 at 11:16

웃웃웃웃웃

12k15 gold badges63 silver badges95 bronze badges

answered Dec 19, 2013 at 10:47

Akhil Sidharth

7461 gold badge6 silver badges16 bronze badges

Comments

adamS · Accepted Answer · 2013-12-19 11:05:45Z

I didn't test this but it should point you in the right direction especially for the second part of your question about minimal range.

SELECT t1.id as id, t1.date_available as date_available, t1.room_id FROM rooms_availble as t1 JOIN rooms as t2 on t1.room_id = t2.id WHERE t1.date_available BETWEEN DATE('2013-12-20') AND DATE('2012-03-31') AND t2.minimal_range <= datediff('2013-12-22', '2012-12-20');

The mysql datediff function will return the number of days between two dates then you can just compare it with the minimal_range from the rooms join table. Also you might consider binding the start and end dates to variables so that you only have to write each date once.

Collectives™ on Stack Overflow

MySQL querying date range

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related