Home > Software design >  I have to create a query that check before inserting if the booking dates don't overlap
I have to create a query that check before inserting if the booking dates don't overlap

Time:06-01

I have a Booking table composed by these attributes: Booking(ID, checkIn, checkOut)

  • ID bigint NOT NULL,
  • checkIn timestamp(0) NOT NULL,
  • checkOut timestamp(0) NOT NULL,
  • primary key(ID)

timestamp(0) datatype is something like this: '2022-12-31 23:59:59'.

and I need to create an INSERT query that checks before inserting if it doesn't exist another query overlapping those checkIn/checkOut dates.

For example I already have in my db this tuple: (1, '2022-12-03 23:59:59', '2022-12-07 23:59:59')

If I insert this tuple (2, '2022-12-04 23:59:59', '2022-12-06 23:59:59') there are no problems for the DB, because ID is unique, but I cannot have two bookings in the iteraval from 12/04 - 12/06

This expression checks if two interval don't overlap (checkIn1 <= checkOut2) and (checkOut1 >= checkIn2)

I'm using PostgreSQL.

Thank you very much

  • Related