query for available place using dates in postgresql/spring boot-CodePudding

I am a junior developer and I am trying to create an application to reserve a seat in an open-space on Spring boot with postgresql.

I want to be able to check if the place is not already booked during the duration desired by the user. I already did the database but I'm blocking on how to proceed with the verification.

example : i have already an event : { id:'1', name :'Pepito', start:'2019-01-01 00:01:00', end:'2019-03-06 00:01:00', desk_id:'16' } start and end columns are timestamp without time zone type.

I want to insert a new event without it happening at the same time as other events having the same desk_id.

CodePudding user response：

To check if a workplace is free by date, you need to run the following query:

select case when count(*) > 0 then false else true end as available from my_table WHERE desk_id = $YOUR_DESK_ID and not (
  (start < '$YOUR_START_DATE' and end < '$YOUR_START_DATE') 
   or
  (start > '$YOUR_END_DATE' and end > '$YOUR_END_DATE') 
);

The query result will return true or false. This way you can check before adding a new record.