How to make an efficient query on a date range?

Question

PostgreSQL

Hi all! A problem is brewing, it is necessary to make it possible to filter by a range of dates with a complete match by dates. Roughly speaking, there is a hotel booking service, the user sets from April 1 to April 10, at the same time, the hotel owner has set in advance what dates he can book a room. It is necessary to filter the numbers by the complete coincidence of dates. It was decided to store the dates in a separate table with a date column that stores a specific day.
There is no good idea how to make a query more efficiently so that it would not have to be sorted out for each day. It seems to me that this will take a very long time with a large number of numbers. Or is there no other way out besides this solution? Thank you for your attention!

Answer 1

So there seems to be no problem.
The hotelier in the table indicates the dates when the room can be booked.
type

outdate                  indate                room
20-01-2015           22-01-2015            125

We do it in just two steps.
1) select all numbers for the free interval
2) Having received the list (it will be small, even for a hotel with 10,000 rooms), we programmatically go through the array of numbers and additionally search for the required time.
This is all approximate, of course, but you can experience it in 20 minutes.

Answer 2

But there are intervals in postgres. you can think about it this way..

Answer 3

From the table with free dates of numbers, select all records whose date falls within the range entered by the user and check that the number of records for each number is equal to the range.

Таблица FreeNumbers:
FreeDate - Дата, когда номер свободен
NumberNo - номер номера

SELECT count(NumberNo), datediff ('dd', @BeginDate, @EndDate), NumberNo
From FreeNumbers
WHERE FreeDate between @BeginDate and @EndDate
group by NumberNo, datediff ('dd', @BeginDate, @EndDate)

MS SQL code, call me.
Thanks, nApoBo3 , for your valuable comment.

Answer 4

Are you starting from one hotel or is it an aggregator.
If from one, then there is no problem at all, even if everything is done very badly.
Let's say there are 400 rooms in a hotel, there are 365 days in a year, in the table we store the Id of the room, the date, the status of the room. This is about 8 bytes per entry (for postgresql), about a megabyte per year.
Past data can be archived immediately.
Query: dates where the number is not among the numbers for which there is occupied in the dates from, to.