Find booking Id between 2 dates SQL (Postgresql)-CodePudding

I have table booking with below records.

 event_type  |      from_date      |       to_date
------------- --------------------- ---------------------
 party       | 2015-09-24 08:00:00 | 2015-09-24 09:30:00
 hangout     | 2015-09-24 09:00:00 | 2015-09-24 10:00:00
 hangout     | 2015-09-24 10:00:00 | 2015-09-24 10:30:00
 dinner      | 2015-09-24 12:00:00 | 2015-09-24 13:30:00

So when user come and select the date and time in the frontend, I will send a date with time as sample above.

Now in the backend I was thinking to query all records using user's selected date and time.

Hence if the selected date and time is exist (query return more than 0) then I will display booking is not available, but if it return 0 record, then booking is available.

How can I achieve that?

CodePudding user response：

If frontend gives two dates and event type:

fe_period_start
fe_period_end
fe_event_type

then query to check if there is no overlaping period shoud look like this

select count(*) from booking 
 where event_type = fe_event_type
   and from_date <= fe_period_end 
   and to_date >= fe_period_start

In this case we find as a conflict also situations where reservation ends in 10:00 and we look for something free from 10:00.

If we accept, that one reservation can end at 10:00 and second can start at 10:00 then inequality in where must be strong.

select count(*) from booking 
  where event_type = fe_event_type
    and from_date < fe_period_end 
    and to_date > fe_period_start