如何确定一个日期范围是否出现在另一个日期范围内的任何时间?[英] How to determine if a date range occurs any time within another date range?

本文是小编为大家收集整理的关于如何确定一个日期范围是否出现在另一个日期范围内的任何时间?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个事件表,该事件表指定start_date和end_date字段的日期范围.我有另一个日期范围,在代码中指定,将本周定义为" week_start"和" week_end".

我想查询本周的所有活动.案例似乎是:

  • 事件在一周内开始并结束
  • 事件在一周之前开始,但在一周内结束
  • 活动在一周内开始,但在一周之后结束
  • 事件在一周之前开始,也在一周之后结束
  • 既不存在的事件,也不忽略本周的事件,

我试图提出一个可以处理所有这些情况的查询.到目前为止,我只能获得处理一周重叠的案件或完全内部的事件;本质上,记录太多或根本没有.

推荐答案

(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.

i. e.如果您的一周开始Sun, 0:00:00,则应以next Sun, 0:00:00结束(不在Sat, 0:00:00上)

此表达式看起来比常用的表达更复杂:

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

,但前者更有效,索引友好.

请参阅我的博客中的这些文章以获取性能比较:

其他推荐答案

您可以这样写下您的状况:

start_date <= week_end AND end_date >= week_start

编辑:这是假设start_date <= end_date和week_start <= week_end(已正确订购),并为您提供大多数DB实现的最佳性能,这是由于不使用或(在某些数据库中可能会造成性能问题)

)

edit2:该解决方案还解决了在间隔之前开始并在间隔结束之前开始的事件的问题.

其他推荐答案

+1 +1 for Pop Catalin,但可惜我没有投票特权.

您想要的限制条件只是表达Allen的"重叠"操作员的标准方法.

附加的SQL警告:如果END_DATE是空白的,请确保将这些列中的nulls视为"时间的终结".

其他功能警告:请确保将'<='v'v'<'的用法调整到记录的时间段是否包括结束日期.

本文地址:https://www.itbaoku.cn/post/597629.html

问题描述

I have an Event table that specifies a date range with start_date and end_date fields. I have another date range, specified in code, that defines the current week as 'week_start' and 'week_end'.

I'd like to query all Events for the week. The cases seem to be:

  • Event begins and ends within the week
  • Event begins before the week, but ends within the week
  • Event begins within the week, but ends after the week
  • Event begins before the week and also ends after the week
  • Events that neither reside within, nor overlap the week at all are ignored

I'm attempting to come up with a query that can handle all these cases. So far I've only been able to get cases that handle the week overlaps, or events that are fully internal; Essentially, too many records, or none at all.

推荐答案

(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:

其他推荐答案

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.

其他推荐答案

+1 for pop Catalin, but alas I have no voting privilege.

The restrict condition you want is just the standard way to express Allen's "OVERLAPS" operator.

Additional SQL caveat : if end_date is nullable, be sure to treat nulls in those columns as "the end of time".

Additional functional caveat : be sure to adapt the usage of '<=' versus '<' to whether or not the recorded time periods include the end date or not.