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'.
我有一个事件表，它指定一个带有start_date和end_date字段的日期范围。我有另一个日期范围，在代码中指定，它将当前周定义为“week_start”和“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.
我正在尝试提出一个查询来处理所有这些情况。到目前为止，我只能得到处理周重叠的情况，或完全内部的事件;本质上，记录太多，或者根本没有。
5 个解决方案
#1
21
(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.
注意，上面的in表达式之间使用只是为了简洁。
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.
,前提是一周。是一个星期。开始+间隔7天。
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)
也就是说，如果你的一周从太阳开始，0:00:00，那么它应该在下一个太阳结束，0:00:00(不是在星期六，0: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
- 重叠的范围:SQL Server
- Overlapping ranges: MySQL
- 重叠的范围:MySQL
- Overlapping ranges: Oracle
- 重叠的范围:甲骨文
#2
3
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)
编辑:这假定start_date <= end_date和week_start <= week_end(有序)，并且由于不使用或(在某些数据库上可能会产生性能问题)，在大多数db实现上都能获得最佳性能
Edit2: this solution also solves the problem of events that begin before the interval and end after the interval.
Edit2:这个解决方案还解决了事件在间隔之前开始，在间隔之后结束的问题。
#3
2
+1 for pop Catalin, but alas I have no voting privilege.
+1的pop - Catalin，但可惜我没有投票权。
The restrict condition you want is just the standard way to express Allen's "OVERLAPS" operator.
您想要的限制条件只是表示Allen的“重叠”运算符的标准方式。
Additional SQL caveat : if end_date is nullable, be sure to treat nulls in those columns as "the end of time".
附加SQL警告:如果end_date是可空的，请确保将这些列中的nulls视为“时间的终结”。
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.
附加的功能警告:请确保将'<='和'<'的用法调整到记录的时间段是否包含结束日期。
#4
0
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
#5
0
(end2 >= start1) && (start2 <= end1) I think would return true for any intersecting date ranges.
(end2 >= start1) && (start2 <= end1)我认为任何相交的日期范围都会返回true。
I found a discussion about this here that I found useful.
我在这里找到了一个关于这个问题的讨论，我觉得很有用。
#1
21
(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.
注意，上面的in表达式之间使用只是为了简洁。
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.
,前提是一周。是一个星期。开始+间隔7天。
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)
也就是说，如果你的一周从太阳开始，0:00:00，那么它应该在下一个太阳结束，0:00:00(不是在星期六，0: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
- 重叠的范围:SQL Server
- Overlapping ranges: MySQL
- 重叠的范围:MySQL
- Overlapping ranges: Oracle
- 重叠的范围:甲骨文
#2
3
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)
编辑:这假定start_date <= end_date和week_start <= week_end(有序)，并且由于不使用或(在某些数据库上可能会产生性能问题)，在大多数db实现上都能获得最佳性能
Edit2: this solution also solves the problem of events that begin before the interval and end after the interval.
Edit2:这个解决方案还解决了事件在间隔之前开始，在间隔之后结束的问题。
#3
2
+1 for pop Catalin, but alas I have no voting privilege.
+1的pop - Catalin，但可惜我没有投票权。
The restrict condition you want is just the standard way to express Allen's "OVERLAPS" operator.
您想要的限制条件只是表示Allen的“重叠”运算符的标准方式。
Additional SQL caveat : if end_date is nullable, be sure to treat nulls in those columns as "the end of time".
附加SQL警告:如果end_date是可空的，请确保将这些列中的nulls视为“时间的终结”。
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.
附加的功能警告:请确保将'<='和'<'的用法调整到记录的时间段是否包含结束日期。
#4
0
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
#5
0
(end2 >= start1) && (start2 <= end1) I think would return true for any intersecting date ranges.
(end2 >= start1) && (start2 <= end1)我认为任何相交的日期范围都会返回true。
I found a discussion about this here that I found useful.
我在这里找到了一个关于这个问题的讨论，我觉得很有用。