3

I have a little problem with a specific functionality. I have a table of reservations for rooms.

Table: Reservations

room (BIGINT), date (DATE)
            4,  2012-09-25
            4,  2012-09-27
            4,  2012-09-30

I need to obtain the dates when room with id = 4 is available between a date range. For example, if i want the dates that the room is available between 2012-09-25 and 2012-10-01 (inclusive) i have to obtain the next result:

date (DATE)
 2012-09-26
 2012-09-28
 2012-09-29
 2012-10-01

Any help will be appreciated. Thanks in advance.

rationalboss
  • 5,330
  • 3
  • 30
  • 50
prueba prueba
  • 652
  • 1
  • 8
  • 26
  • no single query can do that as of this time. take a look at this: http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates – rationalboss Sep 25 '12 at 16:00
  • 1
    A combination of mysql and code will solve your problem or you can write a stored procedure to loop through a date range for a specified interval and return desired results. – Steven Cheng Sep 25 '12 at 16:02

2 Answers2

1

This sort of query is easily handled if you add a table of dates to your database. Fill it with a large number of dates (and add to it when you need to), and just do a right outer join to it to find the missing dates.

If you can't or don't want to add an additional table, you can convert the dates to integer field (representing the number of days since Jan 1, 1970) with this:

SELECT room, UNIX_TIMESTAMP(`date`)/86400 FROM reservations

From there, you can use the techniques in this question or elsewhere to look for gaps in that sequence.

Community
  • 1
  • 1
Ian Clelland
  • 43,011
  • 8
  • 86
  • 87
1

As @steven-cheng said, i had to use the combination of mysql and code because creating a temporary table is too messy. Using the code to get the dates that are available is faster.

prueba prueba
  • 652
  • 1
  • 8
  • 26