如何在mysql中从不可用的日期范围中获取可用的日期范围？

zsohkypk 于 11个月前发布在 Mysql

关注(0)|答案(1)|浏览(188)

我创建了一个存储过程：

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

它返回停车位的保留时间：

从这个表：

我们怎么才能把可用的时间用在这上面呢？
假设每个slot_id都有一个小时范围，
开始：2023-05-30 15:00:00结束：9999-12-30 00:00:00
因为根据上面的图像2023-05-30 18:00:00直到2023-05-30 20:00:00被占用，这意味着唯一可用的时间如下：

2023-05-30 15:00:00至2023-05-30 17:59:59
2023-05-30 20:00:00至9999-12-30 00:00:00
请注意，可能有多个预订在一个单一的插槽，我只需要得到可用的时间。
MySQL/MariaDB：v10.10.2-MariaDB
我已经努力了好几天了，尽我最大的努力去解决它，但对我来说似乎是不可能的：）。
先谢谢你了。

mysql

来源：https://stackoverflow.com/questions/76362625/how-to-get-the-available-date-range-from-unavailable-date-range-in-mysql

1条答案

按热度按时间

piv4azn71#

如果你知道一些概念，逻辑并不复杂。假设你有这些 * 占用 * 日期：
| 开始_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|
| ......这是什么？|......这是什么？|
输入范围：
| @dt1| @dt2|
| - -----|- -----|
| 2023-06-01 12：00：00| 2023-06-04 12：00：00|
然后首先找到与该范围相交的所有占用率。结果将是这4行：该范围从第一行的中间开始并在第四行的中间结束。
现在，您将开始日期和结束日期从结果中提取到单个列中：结束日期应被视为 * 可用 * 时隙的开始日期，反之亦然。将输入的开始和结束日期添加到此列中以确保完整性。
最后，使用lag函数匹配结束日期和开始日期。
类似于：

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

赞(0）回复(0）举报 12个月前

我来回答

如何在mysql中从不可用的日期范围中获取可用的日期范围？

1条答案

相关问题

热门标签

最新问答