CODEWITHSUNDEEP
sqlpostgresql
smiggle
smiggle

Reputation: 1259

Find all available BookingSlots in a certain timespan

I have a table with BookingSlots. Each slot is 15 minutes long.

id |     startTime       | userId  | status
-------------------------------------------
1  | 2022-01-01T01:00:00 | 1       | free
-------------------------------------------
2  | 2022-01-01T01:00:00 | 2       | free
-------------------------------------------
3  | 2022-01-01T01:15:00 | 1       | free
-------------------------------------------
4  | 2022-01-01T01:15:00 | 2       | booked

I want to find all BookingSlots that are free at a given date for a timespan of 15 minutes grouped by the userId. So when the I would run the query at 01:08:00, the result should be:

id |     startTime       | userId  | status
-------------------------------------------
1  | 2022-01-01T01:00:00 | 1       | free
-------------------------------------------
3  | 2022-01-01T01:15:00 | 1       | free

So far my query looks like this:

SELECT
    *
FROM
    "BookingSlot"
WHERE ("startTime" < '2022-01-01T01:08:00'
    AND '2022-01-01T01:08:00' < "startTime" + interval '15 minutes')
    OR("startTime" < timestamp '2022-01-01T01:08:00' + interval '15 minutes'
        AND timestamp '2022-01-01T01:08:00' + interval '15 minutes' < "startTime" + interval '15 minutes')
    AND "status" = 'free'

But the query result includes the row with id 2, which does not match the criteria, because the next slot of user 2 is already booked.

Upvotes: 0

Views: 61

Answers (1)

Edouard
Edouard

Reputation: 7065

You just have to filter on status = 'free and startDate in the right range, and then to select only the userId with 2 rows filtered using the clause HAVING count(*) = 2 :

SELECT unnest(array_agg(id)) AS id
     , unnest(array_agg(startTime)) AS startTime
     , userId
     , unnest(array_agg(status)) AS status
 FROM BookingSlots
WHERE status = 'free'
  AND startTime > '2022-01-01T01:08:00' :: timestamp - interval '15 minutes'
  AND startTime <= '2022-01-01T01:08:00' :: timestamp + interval '15 minutes'
GROUP BY userId
HAVING count(*) = 2

see dbfiddle

Upvotes: 1

Related Questions