给定两个日期范围,确定两个日期是否重叠的最简单或最有效的方法是什么?
例如,假设我们有由DateTime变量StartDate1到EndDate1和StartDate2到EndDate2表示的范围。
给定两个日期范围,确定两个日期是否重叠的最简单或最有效的方法是什么?
例如,假设我们有由DateTime变量StartDate1到EndDate1和StartDate2到EndDate2表示的范围。
当前回答
答案对我来说太简单了,所以我创建了一个更通用的动态SQL语句,用于检查某人是否有重叠的日期。
SELECT DISTINCT T1.EmpID
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.EmpID = T2.EmpID
AND T1.JobID <> T2.JobID
AND (
(T1.DateFrom >= T2.DateFrom AND T1.dateFrom <= T2.DateTo)
OR (T1.DateTo >= T2.DateFrom AND T1.DateTo <= T2.DateTo)
OR (T1.DateFrom < T2.DateFrom AND T1.DateTo IS NULL)
)
AND NOT (T1.DateFrom = T2.DateFrom)
其他回答
下面的查询给出了提供的日期范围(开始和结束日期)与table_name中的任何日期(开始和终止日期)重叠的ID
select id from table_name where (START_DT_TM >= 'END_DATE_TIME' OR
(END_DT_TM BETWEEN 'START_DATE_TIME' AND 'END_DATE_TIME'))
在Microsoft SQL SERVER中-SQL函数
CREATE FUNCTION IsOverlapDates
(
@startDate1 as datetime,
@endDate1 as datetime,
@startDate2 as datetime,
@endDate2 as datetime
)
RETURNS int
AS
BEGIN
DECLARE @Overlap as int
SET @Overlap = (SELECT CASE WHEN (
(@startDate1 BETWEEN @startDate2 AND @endDate2) -- caters for inner and end date outer
OR
(@endDate1 BETWEEN @startDate2 AND @endDate2) -- caters for inner and start date outer
OR
(@startDate2 BETWEEN @startDate1 AND @endDate1) -- only one needed for outer range where dates are inside.
) THEN 1 ELSE 0 END
)
RETURN @Overlap
END
GO
--Execution of the above code
DECLARE @startDate1 as datetime
DECLARE @endDate1 as datetime
DECLARE @startDate2 as datetime
DECLARE @endDate2 as datetime
DECLARE @Overlap as int
SET @startDate1 = '2014-06-01 01:00:00'
SET @endDate1 = '2014-06-01 02:00:00'
SET @startDate2 = '2014-06-01 01:00:00'
SET @endDate2 = '2014-06-01 01:30:00'
SET @Overlap = [dbo].[IsOverlapDates] (@startDate1, @endDate1, @startDate2, @endDate2)
SELECT Overlap = @Overlap
我认为,在以下情况下,两个范围重叠就足够了:
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)
这里有一个可以在本地使用的通用方法。
// Takes a list and returns all records that have overlapping time ranges.
public static IEnumerable<T> GetOverlappedTimes<T>(IEnumerable<T> list, Func<T, bool> filter, Func<T,DateTime> start, Func<T, DateTime> end)
{
// Selects all records that match filter() on left side and returns all records on right side that overlap.
var overlap = from t1 in list
where filter(t1)
from t2 in list
where !object.Equals(t1, t2) // Don't match the same record on right side.
let in1 = start(t1)
let out1 = end(t1)
let in2 = start(t2)
let out2 = end(t2)
where in1 <= out2 && out1 >= in2
let totover = GetMins(in1, out1, in2, out2)
select t2;
return overlap;
}
public static void TestOverlap()
{
var tl1 = new TempTimeEntry() { ID = 1, Name = "Bill", In = "1/1/08 1:00pm".ToDate(), Out = "1/1/08 4:00pm".ToDate() };
var tl2 = new TempTimeEntry() { ID = 2, Name = "John", In = "1/1/08 5:00pm".ToDate(), Out = "1/1/08 6:00pm".ToDate() };
var tl3 = new TempTimeEntry() { ID = 3, Name = "Lisa", In = "1/1/08 7:00pm".ToDate(), Out = "1/1/08 9:00pm".ToDate() };
var tl4 = new TempTimeEntry() { ID = 4, Name = "Joe", In = "1/1/08 3:00pm".ToDate(), Out = "1/1/08 8:00pm".ToDate() };
var tl5 = new TempTimeEntry() { ID = 1, Name = "Bill", In = "1/1/08 8:01pm".ToDate(), Out = "1/1/08 8:00pm".ToDate() };
var list = new List<TempTimeEntry>() { tl1, tl2, tl3, tl4, tl5 };
var overlap = GetOverlappedTimes(list, (TempTimeEntry t1)=>t1.ID==1, (TempTimeEntry tIn) => tIn.In, (TempTimeEntry tOut) => tOut.Out);
Console.WriteLine("\nRecords overlap:");
foreach (var tl in overlap)
Console.WriteLine("Name:{0} T1In:{1} T1Out:{2}", tl.Name, tl.In, tl.Out);
Console.WriteLine("Done");
/* Output:
Records overlap:
Name:Joe T1In:1/1/2008 3:00:00 PM T1Out:1/1/2008 8:00:00 PM
Name:Lisa T1In:1/1/2008 7:00:00 PM T1Out:1/1/2008 9:00:00 PM
Done
*/
}
这是我的Java解决方案,它也适用于无限区间
private Boolean overlap (Timestamp startA, Timestamp endA,
Timestamp startB, Timestamp endB)
{
return (endB == null || startA == null || !startA.after(endB))
&& (endA == null || startB == null || !endA.before(startB));
}