如何确定日期范围是否在其他日期范围内的任何时间发生？ [英] How to determine if a date range occurs any time within another date range?

问题描述

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

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

• 活动在一周内开始和结束




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


• 活动在周前开始，


b $ b

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

解决方案

 （event.start BETWEEN week.start AND week.end）
 OR 
（week.start BETWEEN event.start AND event.end）
  

简单来说，一个星期开始于活动期间，或一个活动在一周内开始。





p>






活动开始并在一周内结束







活动开始前一周，但结束于一周




活动开始。




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




活动在一周内开始。




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




$





请注意 BETWEEN code>





严格表达式如下：

 （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天。





我。 e。如果你每周开始 Sun，0:00:00 ，那么它应该在下一个太阳，0:00:00结束（不在星期六，0:00:00 ）





这个表达式看起来比是常用的：

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

，但前者效率更高且更易于索引。





查看我的博客中有关性能比较的这些文章：






• 重叠范围：SQL Server


• 重叠范围：MySQL li>
  
• 重叠范围：Oracle





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:

• Overlapping ranges: SQL Server
• Overlapping ranges: MySQL
• Overlapping ranges: Oracle

这篇关于如何确定日期范围是否在其他日期范围内的任何时间发生？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！