假设我有一个名为events
的表,其中列为
INT id
DATETIME start_time
DATETIME end_time
我如何找到从start_time到end_time的时间范围的任何部分落在某个小时范围内的所有行?
例如,我可能想要查找在8-10pm时间范围内的事件的某些部分。
比如
select * from events where (hour(start_time) IN (20, 21, 22)) or (hour(end_time) IN (20, 21, 22))
可以工作,除了在晚上8点之前开始并在晚上11点之后结束的事件。
最后,它必须跨越一天的界限,所以像23-2(晚上11点到凌晨2点)这样的范围也应该可行。
不幸的是,我无法修改表的模式
假设start_time和end_time字段是datetime字段,则
SELECT *
FROM events
WHERE (start_time <= $event_end_time) AND (end_time >= $event_start_time)
这有点违反直觉,但如果你在时间轴上看它:
A&B是你的极限栅栏。X&Y是start_time和end_time字段:
A B
p = ----------- event falls outside the boundaries
X Y
A B
q = ----------- partial overlap
X Y
A B
r = ----------- partial overlap again
X Y
A B
s = ----------- full overlap
X Y
A B
t = ----------- outside boundaries
X Y
你感兴趣的是Q、R和s。你会注意到,对于这3种情况,Y总是>= A, X总是<= B
我设法使这个工作与一天的界限要求。它很丑,但似乎涵盖了所有的情况。
虽然您可以编写一个存储过程来完成它,但在Java中更容易表示,因此:
public void checkEvents (int[][] events, int startHour, int endHour)
final int START = 0;
final int END = 1;
final int HOURS_PER_DAY = 24;
for (int[] event : events) {
int start = startHour;
int end = (endHour < startHour) ? endHour + HOURS_PER_DAY : endHour;
int eventStart = event[START];
int eventEnd = (event[END] < event[START]) ? event[END] + HOURS_PER_DAY : event[END];
if (checkOverlap(result, event, start, end, eventStart, eventEnd))
|| (checkOverlap(result, event, start, end, eventStart + HOURS_PER_DAY, eventEnd + HOURS_PER_DAY))
|| (checkOverlap(result, event, start + HOURS_PER_DAY, end + HOURS_PER_DAY, eventStart, eventEnd))
|| (!checkOverlap(result, event, start + HOURS_PER_DAY, end + HOURS_PER_DAY, eventStart + HOURS_PER_DAY, eventEnd + HOURS_PER_DAY);
System.out.println("overlapped!");
}
}
private boolean checkOverlap(ArrayList<int[]> result, int[] event, int start, int end, int eventStart, int eventEnd) {
if ((eventStart >= start && eventStart < end) || (eventStart < start && eventEnd > start)) {
return true;
}
return false;
}