Home Checking for overlapping bookings in Postgresql
Reply: 0

Checking for overlapping bookings in Postgresql

user1012
1#
user1012 Published in April 21, 2018, 3:26 pm

I have a booking table for cars that can be hired. There are multiples cars and multiple bookings. I want to create a query to check for any overlaps (bookings where the same car has been hired and the dates clash) in bookings.

At the moment I have the following query:

SELECT bi.id, car_id, booking_id 
from booking_bookingitem bi 
WHERE (
SELECT count(*) FROM booking_bookingitem bt 
WHERE (
date_start - interval '1 days', date_end + interval '1 days'
) OVERLAPS (now(), now() + INTERVAL '30 days') 
AND bt.car_id = bi.car_id) > 1 
ORDER BY car_id, booking_id

I have added 1 day offset either side of the start (date_start) and end (date_end) to allow for the car to be returned, cleaned, etc. The 30 day interval is to check bookings 30 days in to the future.

I think I'm close but I am getting some abnormalities when testing.

Can anyone spot what I've done wrong, or is there a better way to do this?

Thanks in advance!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.33022 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO