Untitled

SELECT
    r.id AS room_id,
    s.start_time AS start,
    IFNULL(TIME(MIN(b.date_from)), s.end_time) AS end
FROM
    rooms r
JOIN
    schedules s ON r.id = s.room_id AND s.day_of_week = DAYOFWEEK(:date1) - 1
LEFT JOIN
    bookings b ON r.id = b.room_id AND DATE(b.date_from) = :date1 AND b.status NOT IN ($status)
GROUP BY
    r.id, s.start_time, s.end_time
HAVING
    TIMEDIFF(end, start) >= '01:00:00'

UNION

SELECT
    b1.room_id,
    TIME(b1.date_to) AS start,
    (
        SELECT
            TIME(MIN(b2.date_from))
        FROM
            bookings b2
        WHERE
            b2.status NOT IN ($status)
            AND DATE(b2.date_from) = DATE(b1.date_to)
            AND b2.date_from >= b1.date_to
            AND b1.room_id = b2.room_id
    ) AS end
FROM
    bookings b1
WHERE
    DATE(b1.date_from) = :date2 AND b1.status NOT IN ($status)
HAVING
    start IS NOT NULL
    AND end IS NOT NULL
    AND TIMEDIFF(end, start) >= '01:00:00'

UNION

SELECT
    r.id AS room_id,
    IF(
        COUNT(b.date_from) = 0,
        s.end_time,
        TIME(MAX(b.date_to))
    ) AS start,
    s.end_time AS end
FROM
    rooms r
JOIN
    schedules s ON r.id = s.room_id AND s.day_of_week = DAYOFWEEK(:date3) - 1
LEFT JOIN
    bookings b ON r.id = b.room_id AND DATE(b.date_from) = :date3 AND b.status NOT IN ($status)
GROUP BY
    r.id, s.start_time, s.end_time
HAVING
    TIMEDIFF(end, start) >= '01:00:00'