是否有任何MySQL函数可获取开始日期或结束日期在给定开始日期和结束日期之间的所有行?
问题描述：
我有一个事件表，其中记录了一个开始和结束时间，作为MySQL DATETIME对象(格式为YYYY-MM-DD HH:MM:SS.我想查找在特定日期范围内发生的所有事件.但是，事件可以跨越多天(并超出我的日期范围，但如果它们与我的日期范围重叠1秒或更长时间，我想退还它们.)
I have a table of events with a recorded start and end time as a MySQL DATETIME object (in the format YYYY-MM-DD HH:MM:SS. I want to find all events that occur in a specific date range. However, events can span multiple days (and go outside of my date range, but I want to return them if they even overlap by 1 second or more with my date range).
建议?
答
这将查找范围内完全包含的每个事件:
This will find every event that is completely contained inside the range:
SELECT * FROM table WHERE start_date BETWEEN start_of_range AND end_of_range
AND stop_date BETWEEN start_of_range AND end_of_range
这将查找事件的任何部分与范围的任何部分重叠的所有事件:
This will find any events where any part of the event overlaps any part of the range:
SELECT * FROM table WHERE start_date <= end_of_range
AND stop_date >= start_of_range