当前位置： 文江博客话题详情

比较 mySQL 中的多个日期

发布于 2024-11-02 16:15:55 字数 5676 浏览 8 评论 0原文

我们正在开发一个需要支持分割预约的预订系统。这些预约有上半场、休息时间（在此期间可以预订其他事情），然后是下半场。

该系统还支持具有标准开始和结束时间的正常预订。

我们需要检查数据库，看看是否有任何现有预订与我们希望进行的预订重叠。

我们之前已经在 PHP 中完成了此操作，但在这种情况下需要仅在 mysql 中完成。

预订表有：

startDate（始终） splitStartDate（有时） splitEndDate（有时） endDate（始终）

当 splitStartDate 和 splitEndDate 未用于预订时，它们的值为 0000-00-00 00:00:00

我们开始尝试使用 ifs 构建 mysql 语句，但它看起来长得离谱。

    "SELECT *
     FROM bookings WHERE
    (
        (
            IF(
                splitStartDate != "0000 00:00:00",
                IF(
                    ((CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= startDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < splitStartDate))
                    OR
                    ((CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= splitEndDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < endDate))
                    , 1, 0
                ),
                IF(
                    (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < endDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= startDate), 1, 0        
                )
            ) = 1
        )
        OR
        (
            IF(
                splitStartDate != "0000-00-00 00:00:00",
                IF(
                    ((CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > startDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= splitStartDate))
                    OR
                    ((CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > splitEndDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= endDate))
                    , 1, 0
                ),
                IF(
                    (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > startDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= endDate), 1, 0        
                )
            ) = 1           
        )
        OR 
        (

我们知道必须有一种更简单的方法来做到这一点，但我们对它是什么感到困惑。我们希望获得有关如何为此构建 mysql 查询的任何建议。

在检查可用性时，我们一直在运行以下检查（很抱歉这花了多长时间，但这就是问题所在）：

------------
all simple

requested startDate is in-between existing startDate and existing endDate
requested endDate is in-between existing startDate and existing endDate
existing startDate is in-between requested startDate and requested endDate
existing endDate is in-between requested startDate and requested endDate



-------------
advanced desired / simple existing

requested startDate is in-between existing startDate and existing endDate
requested splitStartDate is in-between existing startDate and existing endDate
existing startDate is in-between requested startDate and requested splitStartDate
existing endDate is in-between requested startDate and requested splitStartDate

requested splitEndDate is in-between existing startDate and existing endDate
requested endDate is in-between existing startDate and existing endDate
existing startDate is in-between requested splitEndDate and requested endDate
existing endDate is in-between requested splitEndDate and requested endDate


------
simple desired / advanced existing

requested startDate is in-between existing startDate and existing splitstartDate
requested endDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested startDate and requested endDate
existing splitstartDate is in-between requested startDate and requested endDate

requested startDate is in-between existing splitEndDate and existing endDate
requested endDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested startDate and requested endDate
existing endDate is in-between requested startDate and requested endDate

-----
advanced both

1st 1/2 both
requested startDate is in-between existing startDate and existing splitstartDate
requested splitStartDate  is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested startDate and requested splitStartDate
existing splitstartDate  is in-between requested startDate and requested splitStartDate

2/2 desired  1/2 existing
requested splitEndDate  is in-between existing startDate and existing splitstartDate
requested endDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested splitEndDate and requested endDate
existing splitstartDate is in-between requested splitEndDate and requested endDate

1/2 desired 2/2 existing
requested startDate is in-between existing splitEndDate and existing endDate
requested splitStartDate  is in-between existing splitEndDate and existing endDate
existing splitEndDate  is in-between requested startDate and requested splitStartDate
existing endDate is in-between requested startDate and requested splitStartDate

2nd 1/2 all
requested splitEndDate is in-between existing splitEndDate and existing endDate
requested endDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested splitEndDate and requested endDate
existing endDate is in-between requested splitEndDate and requested endDate

非常感谢

原文

We are working on a booking system that needs to support split appointments. These appointments have a first half, a break during which something else can be booked and then the second half.

The system also supports normal bookings which have a standard start and end.

We need to check the database to see if there are any existing bookings that overlap with a booking we wish to make.

We have done this in PHP before but need to do it solely in mysql in this situation.

the bookings table has:

startDate (always)
splitStartDate (sometimes)
splitEndDate (sometimes)
endDate (always)

When the splitStartDate and splitEndDate are not being used for a booking they have a value of 0000-00-00 00:00:00

We started trying to build the mysql statement out of ifs, but it seems ridiculously long.

    "SELECT *
     FROM bookings WHERE
    (
        (
            IF(
                splitStartDate != "0000 00:00:00",
                IF(
                    ((CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= startDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < splitStartDate))
                    OR
                    ((CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= splitEndDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < endDate))
                    , 1, 0
                ),
                IF(
                    (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < endDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= startDate), 1, 0        
                )
            ) = 1
        )
        OR
        (
            IF(
                splitStartDate != "0000-00-00 00:00:00",
                IF(
                    ((CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > startDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= splitStartDate))
                    OR
                    ((CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > splitEndDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= endDate))
                    , 1, 0
                ),
                IF(
                    (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > startDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= endDate), 1, 0        
                )
            ) = 1           
        )
        OR 
        (

We know there has to be a much simpler way to do this but have been stumped as to what it is. We'd love any suggestions on how to build a mysql query for this.

When checking the availability we have been running the following checks (Sorry for how long this is, but that's the problem):

------------
all simple

requested startDate is in-between existing startDate and existing endDate
requested endDate is in-between existing startDate and existing endDate
existing startDate is in-between requested startDate and requested endDate
existing endDate is in-between requested startDate and requested endDate



-------------
advanced desired / simple existing

requested startDate is in-between existing startDate and existing endDate
requested splitStartDate is in-between existing startDate and existing endDate
existing startDate is in-between requested startDate and requested splitStartDate
existing endDate is in-between requested startDate and requested splitStartDate

requested splitEndDate is in-between existing startDate and existing endDate
requested endDate is in-between existing startDate and existing endDate
existing startDate is in-between requested splitEndDate and requested endDate
existing endDate is in-between requested splitEndDate and requested endDate


------
simple desired / advanced existing

requested startDate is in-between existing startDate and existing splitstartDate
requested endDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested startDate and requested endDate
existing splitstartDate is in-between requested startDate and requested endDate

requested startDate is in-between existing splitEndDate and existing endDate
requested endDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested startDate and requested endDate
existing endDate is in-between requested startDate and requested endDate

-----
advanced both

1st 1/2 both
requested startDate is in-between existing startDate and existing splitstartDate
requested splitStartDate  is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested startDate and requested splitStartDate
existing splitstartDate  is in-between requested startDate and requested splitStartDate

2/2 desired  1/2 existing
requested splitEndDate  is in-between existing startDate and existing splitstartDate
requested endDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested splitEndDate and requested endDate
existing splitstartDate is in-between requested splitEndDate and requested endDate

1/2 desired 2/2 existing
requested startDate is in-between existing splitEndDate and existing endDate
requested splitStartDate  is in-between existing splitEndDate and existing endDate
existing splitEndDate  is in-between requested startDate and requested splitStartDate
existing endDate is in-between requested startDate and requested splitStartDate

2nd 1/2 all
requested splitEndDate is in-between existing splitEndDate and existing endDate
requested endDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested splitEndDate and requested endDate
existing endDate is in-between requested splitEndDate and requested endDate

Thanks so much

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

拧巴小姐 2024-11-09 16:15:55

我的猜测是，如果您清理了正在执行的日期操作内容，您会喜欢您的查询。

将其移至 mysql 变量：

SET @REQUEST = (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\");

然后您的代码如下所示：

        IF(
            splitStartDate != "0000 00:00:00",
            IF(
                (@REQUEST >= startDate AND @REQUEST < splitStartDate)
                OR
                (@REQUEST >= splitEndDate) AND @REQUEST < endDate))
                , 1, 0
            ),
            IF(
                (@REQUEST < endDate AND @REQUEST >= startDate), 1, 0        
            )
        ) = 1

这并非不合理。使用“X = 1”对我来说看起来像是一个 where 子句，所以我更愿意看到

select count(*)
from bookings
where
(
    splitStartDate is not null
        and (
             (@REQUEST >= startDate AND @REQUEST < splitStartDate)
             OR
             (@REQUEST >= splitEndDate) AND @REQUEST < endDate))
        )
)
OR
(
    splitStartDate is null
        and (
            @REQUEST < endDate AND @REQUEST >= startDate
        )
)

祝你好运！

My guess is that if you cleanup the date manipulation stuff you're doing, you'll like your query.

Move it to a mysql variable:

SET @REQUEST = (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\");

and then your code looks like this:

        IF(
            splitStartDate != "0000 00:00:00",
            IF(
                (@REQUEST >= startDate AND @REQUEST < splitStartDate)
                OR
                (@REQUEST >= splitEndDate) AND @REQUEST < endDate))
                , 1, 0
            ),
            IF(
                (@REQUEST < endDate AND @REQUEST >= startDate), 1, 0        
            )
        ) = 1

Which is not unreasonable. Using "X = 1" looks like a where clause to me, so I'd prefer to see

select count(*)
from bookings
where
(
    splitStartDate is not null
        and (
             (@REQUEST >= startDate AND @REQUEST < splitStartDate)
             OR
             (@REQUEST >= splitEndDate) AND @REQUEST < endDate))
        )
)
OR
(
    splitStartDate is null
        and (
            @REQUEST < endDate AND @REQUEST >= startDate
        )
)

Good luck!

回复 原文
~没有更多了~

关于作者

懒的傷心

暂无简介

文章
评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

意料之外

文章 0 评论 0

sevendreamyang

文章 0 评论 0

╃暔鸢囄儚メ

文章 0 评论 0

两相知

文章 0 评论 0

_失温

文章 0 评论 0

遗心遗梦遗幸福

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文