CODEWITHSUNDEEP
phpmysqldatedatetimedate-range
Rob
Rob

Reputation: 738

MySQL Check if time range falls outside of given time slots for day

Let's say I have a bookings table:

id|booking_ref|start_datetime      |end_datetime
-------------------------------------------------------
01|ABC        |2018-01-01 09:30:00 |2018-01-01 10:00:00
02|DEF        |2018-01-01 11:00:00 |2018-01-01 12:00:00
03|GHI        |2018-01-01 13:00:00 |2018-01-01 13:15:00

If I provide given time periods when these bookings are allowed to take place, how would I check if any bookings fall within the times when bookings aren't allowed?

For example allowed time slots are from:

2018-01-01 09:00:00 to 2018-01-01 10:30:00
2018-01-01 11:00:00 to 2018-01-01 13:30:00
2018-01-01 14:00:00 to 2018-01-01 17:00:00

All of the bookings in the example table fall between the allowed time slots.

How would you write a query to check if there are any bookings for a given day which doesn't fall into any of the allowed time periods?

I've tried the usual start_datetime >= and end_datetime <= etc. but can't figure it out.

Upvotes: 0

Views: 684

Answers (2)

Rob
Rob

Reputation: 738

Figured it out, this query will find any date ranges that fall outside of the given ranges in part or in whole. Hard-coded in the end.

SELECT
  *
FROM
  mi_bookings b
WHERE NOT (
    (b.start_datetime BETWEEN '2018-01-01 09:00:00' AND '2018-01-01 10:30:00') AND (b.end_datetime BETWEEN '2018-01-01 09:00:00' AND '2018-01-01 10:30:00')     
  OR 
    (b.start_datetime BETWEEN '2018-01-01 11:00:00' AND '2018-02-05 13:30:00') AND (b.end_datetime BETWEEN '2018-01-01 11:00:00' AND '2018-01-01 13:30:00')
  OR 
    (b.start_datetime BETWEEN '2018-01-01 14:00:00' AND '2018-01-01 17:00:00') AND (b.end_datetime BETWEEN '2018-01-01 14:00:00' AND '2018-01-01 17:00:00')
)

Upvotes: 0

shawnt00
shawnt00

Reputation: 17953

select * from bookings b
where exists (
    select 1 from disallowed d
    where b.start <= d.end and d.start <= b.end
);

One could also just hard-code those values:

select * from bookings b
where b.start1 <= '2018-01-01 10:30:00' and '2018-01-01 09:00:00' <= b.end1
   or b.start1 <= '2018-01-01 10:30:00' and '2018-01-01 11:00:00' <= b.end1
   or b.start1 <= '2018-01-01 17:00:00' and '2018-01-01 14:00:00' <= b.end1;

Upvotes: 1

Related Questions