在议程中查找第一个空闲日期 [英] Find first free date in agenda

问题描述

在我的组件中，我创建了一个议程，用户可以在其中保存他们的约会.
议程表非常简单:其中包含标题，描述和开始/结束日期时间字段.

in my component I have created an agenda, where user can save their appointments.
Agenda table is quite simple: there is a title, description and start/end datetime fields.

当用户添加新事件时，我想用第一个空白点提示他.

When a user adds a new event, I'd wish to hint him with the first empty spot.

我该如何实现?
单次查询/束查询是否有可能，或者我必须创建一个循环，直到找到第一个空位?

How can I achieve that?
Is that possible with a single/bunch queries, or I have to create a loop until I find the first empty spot?

例如，这是我的桌子:

|  ID |       Start date     |       End date      |
|  1  | 2012-06-14 09:00:00  | 2012-06-14 09:32:00 |
|  2  | 2012-06-14 15:00:00  | 2012-06-14 15:45:00 |
|  3  | 2012-06-14 18:20:00  | 2012-06-14 18:55:00 |

第一个空闲日期时间应为2012-06-14 09:33:00，如何获取此日期?

The first free datetime should be 2012-06-14 09:33:00, how can I fetch this date?

推荐答案

在一个查询中要做的有趣的挑战:)花了我一段时间，但我提出了解决方案.基于以下假设:

Interesting challenge to be done in one query :) Took me a while but I came with solution. With these assumptions:

• 最短预约时间为30分钟
• 预约在一天之内开始和结束
• 一天开始于9:00，结束于17:00
• 结束时间和开始时间之间的最小间隔为1分钟

有4种情况需要考虑:

1. 早上有一些空闲时间
2. 白天有一些空闲时间
3. 第一个空闲时段是第二天在db中的最后一次约会
4. 从现在开始您将拥有所有可用的空位

所以您必须在这些日期中选择最少的形式.

So yo have to select minimum form these dates.

查询:

SELECT
    MIN(next_start_date) AS next_start_date
FROM
( 
    (
        SELECT 
            DATE_FORMAT(t1.start_date,'%Y-%m-%d 09:00:00') AS next_start_date
        FROM
            agenda t1
        WHERE
            t1.start_date > NOW()
        AND
            TIME(t1.start_date) > '09:30:00'
        AND
            NOT EXISTS(
                SELECT 1 FROM agenda t2 WHERE DATE(t2.start_date) = DATE(t1.start_date) AND TIME(t2.start_date) <= '09:30:00'
            )
        LIMIT 1
    )
    UNION
    (
        SELECT
            t3.end_date + INTERVAL 1 MINUTE AS next_start_date
        FROM
            agenda t3
        WHERE
            t3.start_date > NOW()
        AND
            TIME(t3.start_date) >= '09:00:00'
        AND
            TIME(t3.end_date) < '16:30:00'
        AND NOT EXISTS
            (SELECT 1 FROM agenda t4 WHERE TIMESTAMPDIFF(MINUTE,t3.end_date,t4.start_date) BETWEEN 0 AND 30 )
        ORDER BY
            t3.start_date ASC
        LIMIT 1
    )
    UNION
    (
        SELECT CONCAT(CAST(DATE((SELECT MAX(t5.start_date) + INTERVAL 1 DAY FROM agenda t5 WHERE t5.start_date > NOW())) AS CHAR), ' 09:00:00') AS next_start_date
    )
    UNION
    (
        SELECT 
            IF( 
                TIME(NOW()) < '09:00:00', 
                DATE_FORMAT(NOW(),'%Y-%m-%d 09:00:00'), 
                IF(
                    TIME(NOW()) < '16:30', 
                    NOW(),
                    DATE_FORMAT(NOW() + INTERVAL 1 DAY,'%Y-%m-%d 09:00:00' ) 
                )
            ) AS next_start_date
        FROM
            (SELECT 1) t6
        WHERE NOT EXISTS(
            SELECT 1 FROM agenda t7 WHERE t7.start_date > NOW()
        )
        LIMIT 1
    )
) t

当然这不是完美的-当第二天出现下一个空闲时段时，很可能是星期六或下一天休息.考虑到这一点，最好的方法是检查返回的是否是有效的工作日，如果不是，则用下一个有效工作日的日期字符串替换NOW()重复查询.

Of course it is not perfect - when there next free slot occurs in the next day, there's a chance that it is Saturday or other day off from work. Taking it into consideration, the best way will be to check if returned is valid work day, if not then repeat the query with NOW() replaced by datestring of next valid work day.

这篇关于在议程中查找第一个空闲日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！