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

如何确定一个日期范围是否出现在另一个日期范围内的任何时间？

发布于 2024-07-16 09:30:10 字数 404 浏览 7 评论 0原文

我有一个事件表，它指定带有 start_date 和 end_date 字段的日期范围。我有另一个在代码中指定的日期范围，它将当前周定义为“week_start”和“week_end”。

我想查询本周的所有活动。情况似乎是：

事件在一周内开始并结束
事件在一周之前开始，但在一周内结束
事件在一周内开始，但在一周之后结束
事件在一周之前开始，也在一周之后结束
事件既不在一周之内，也不与一周重叠的部分将被忽略

我正在尝试提出一个可以处理所有这些情况的查询。到目前为止，我只能得到处理每周重叠的案例，或者完全内部的事件；本质上，记录太多，或者根本没有。

原文

分享到QQ

分享到微博

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

发布评论

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

明月夜 2024-07-23 09:30:10

(event.start BETWEEN week.start AND week.end)
OR
(week.start BETWEEN event.start AND event.end)

简单来说，要么一周在事件期间开始，要么事件在一周内开始。

让我们检查一下：

活动在一周内开始和结束

活动在一周内开始。

活动在本周之前开始，但在本周内结束

本周在活动期间开始。

活动在一周内开始，但在一周后结束

活动在一周内开始。

活动在周前开始，并在周后结束

周在事件期间开始。

请注意，上面表达式中的 BETWEEN 只是为了简洁起见。

严格的表达式如下所示：

(event.start >= week.start AND event.start < week.end)
OR
(week.start >= event.start AND week.start < event.end)

，前提是 week.end 是 week.start + INTERVAL 7 DAY。

IE。如果您的一周从周日 0:00:00 开始，那么它应该在下一个周日 0:00:00 结束（而不是在 周六 0:00： 00:00)

这个表达式看起来比常用的表达式更复杂：

event.start < week.end AND event.end > week.start

，但前者更高效且索引友好。

请参阅我的博客中的这些文章以进行性能比较：

(event.start BETWEEN week.start AND week.end)
OR
(week.start BETWEEN event.start AND event.end)

In simple words, either a week starts during the event, or an event starts during the week.

Let's check it:

Event begins and ends within the week

The event starts during the week.

Event begins before the week, but ends within the week

The week starts during the event.

Event begins within the week, but ends after the week

The event starts during the week.

Event begins before the week and also ends after the week

The week starts during the event.

Note that BETWEEN in expressions above is used just for the sake of brevity.

Strict expression looks like this:

(event.start >= week.start AND event.start < week.end)
OR
(week.start >= event.start AND week.start < event.end)

, provided that week.end is a week.start + INTERVAL 7 DAY.

I. e. if you week starts of Sun, 0:00:00, then it should end on next Sun, 0:00:00 (not on Sat, 0:00:00)

This expression looks more complex than the one which is commonly used:

event.start < week.end AND event.end > week.start

, but the former is more efficient and index friendly.

See these articles in my blog for performance comparisons:

回复收藏 0 原文

等风来 2024-07-23 09:30:10

您可以这样写您的条件：

start_date <= week_end AND end_date >= week_start

编辑：这假设 start_date <= end_date 和 week_start <= week_end （正确排序），并且由于不使用 OR （在某些数据库上可能会创建性能问题）

Edit2：此解决方案还解决了事件在间隔之前开始并在间隔之后结束的问题。

You could write your condition like this:

start_date <= week_end AND end_date >= week_start

Edit: this assumes start_date <= end_date and week_start <= week_end ( are properly ordered) and gives you the best performance on most db implementations due to not using OR (which on some databases may create performance issues)

Edit2: this solution also solves the problem of events that begin before the interval and end after the interval.

回复收藏 0 原文

疏忽 2024-07-23 09:30:10

为流行卡塔林+1，但可惜我没有投票权。

您想要的限制条件只是表达 Allen 的“OVERLAPS”运算符的标准方式。

其他 SQL 警告：如果 end_date 可为空，请务必将这些列中的空值视为“时间结束”。

附加功能警告：请务必调整“<=”与“<”的用法记录的时间段是否包括结束日期。

回复收藏 0 原文

少女净妖师 2024-07-23 09:30:10

为了...

where start_date >= week_start and end_date <= week_end
where start_date <= week_start and end_date >= week_start and end_date <= week_end
where start_date >= week_start and start_date <= week_end and end_date > week_end
where start_date < week_start and end_date > week_end

In order...

where start_date >= week_start and end_date <= week_end
where start_date <= week_start and end_date >= week_start and end_date <= week_end
where start_date >= week_start and start_date <= week_end and end_date > week_end
where start_date < week_start and end_date > week_end

回复收藏 0 原文