How to get the available date range from unavailable date range in mysql?

Question

I created a stored procedure:

CREATE PROCEDURE `CheckSlotAvailability`(IN slotId INT)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tempres1;
    CREATE TEMPORARY TABLE tempres1
    SELECT
        `slot_id`,
        `customer_id`,
        IFNULL(`start`, 'empty') AS `start`,
        IFNULL(`end`, 'empty') AS `end`,
        IFNULL(`in`, 'empty') AS `in`,
        IFNULL(`out`, 'empty') AS `out`,
        `parking_type`,
        `status`
    FROM reservations;

    DROP TEMPORARY TABLE IF EXISTS tempres2;
    CREATE TEMPORARY TABLE tempres2
    SELECT
        slot_id,
        `customer_id`,
        MIN(`start`) AS `start`,
        MAX(`end`) AS `end`,
        MIN(`in`) AS `in`,
        MAX(`out`) AS `out`,
        `parking_type`,
        `status`
    FROM tempres1
    GROUP BY slot_id, customer_id;

    DROP TEMPORARY TABLE IF EXISTS tempres3;
    CREATE TEMPORARY TABLE tempres3
    SELECT * FROM tempres2 WHERE slot_id = slotId;

    SELECT * FROM TEMPRES3;
END

It returns reserved hours of a parking slot:

from this table:

How do we get the available hours out of this?

let say, I have a range of hour for each slot_id,

start: 2023-05-30 15:00:00 end: 9999-12-30 00:00:00

since based on the image above 2023-05-30 18:00:00 until 2023-05-30 20:00:00 is occupied, which means the only available hours are as follows:

1. 2023-05-30 15:00:00 until 2023-05-30 17:59:59
2. 2023-05-30 20:00:00 until 9999-12-30 00:00:00

Please note that there could be multiple reservation in a single slot, I just need to get the hours available.

MySQL/MariaDB: v10.10.2-MariaDB

been working on it for days trying my best to solve it, but seems impossible for me :(.

Thanks in advance.

Answer 1

The logic is not that complicated if you know some concepts. Suppose you have these occupied dates:

start_dt	end_dt
...	...
2023-06-01 06:00:00	2023-06-01 18:00:00
2023-06-02 06:00:00	2023-06-02 18:00:00
2023-06-03 06:00:00	2023-06-03 18:00:00
2023-06-04 06:00:00	2023-06-04 18:00:00
...	...

And this input range:

@dt1	@dt2
2023-06-01 12:00:00	2023-06-04 12:00:00

Then first you find all occupancy that intersect the range. The result will be those 4 rows: the range starts in the middle of 1st row and ends in the middle of 4th row.

Now you pull the start and end dates from the result into a single column: the end date should be treated as the start date of an available slot and vice-versa. Add the input start and end dates into this column for cmpleteness.

Finally, use lag function to match end date with start.

Something like:

set @dt1 = '2023-06-01 12:00:00';
set @dt2 = '2023-06-04 12:00:00';
with cte1(dt, type) as (
    select start_dt, 'e' from t where @dt2 > start_dt and end_dt > @dt1
    union all
    select end_dt, 's' from t where @dt2 > start_dt and end_dt > @dt1
    union all
    select @dt1, 's'
    union all
    select @dt2, 'e'
), cte2(dt1, dt2) as (
    select dt, case when type = 's' and lead(type) over (order by dt) = 'e' then lead(dt) over (order by dt) end
    from cte1
)
select *
from cte2
where dt2 is not null
order by dt1

DB<>Fiddle