Reputation: 338
Is there an easy way to get the overlap in minutes of two date ranges in SQL? I'm using PostgreSQL.
Upvotes: 3
Views: 2113
Reputation: 1
**
Declare @Table Table
(
RowId Int Identity(1, 1) Not Null,
Id NChar(3) Not Null,
StartDate DATETIME Not Null,
EndDate DATETIME Not Null
);
Insert Into @Table (Id, StartDate, EndDate)
Select 'id1', '20131210 10:10', '20131220 10:10' Union All
Select 'id1', '20131211', '20131215' Union All
Select 'id1', '20131201', '20131205' Union All
Select 'id1', '20131206', '20131208' Union All
Select 'id1', '20131225 10:10', '20131225 10:11'
Select *
From @Table;
With Overlaps (OverlapRowId, BaseRowId, OStart, OEnd, BStart, BEnd)
As
(
Select Overlap.RowId, Base.RowId, Overlap.StartDate, Overlap.EndDate, Base.StartDate, Base.EndDate
From @Table As Base
Inner Join @Table As Overlap On Overlap.Id = Base.Id
Where (((Overlap.StartDate > Base.StartDate) And (Overlap.StartDate < Base.EndDate))
Or ((Overlap.StartDate = Base.StartDate) And (Overlap.EndDate > Base.EndDate)))
And (Base.RowId != Overlap.RowId)
)
-- Remove records that were found to cause overlap issues.
Delete T
From @Table As T
Inner Join
(
Select O.OverlapRowId
From Overlaps As O
Left Join Overlaps As Fp On Fp.OverlapRowId = O.BaseRowId
Where (Fp.OverlapRowId Is Null)
) As SubQuery On SubQuery.OverlapRowId = T.RowId;
-- Select the valid options.
Select RowId, Id, StartDate, EndDate
From @Table where StartDate<EndDate;
Go
**
Upvotes: 0
Reputation: 14937
This function will handle any input of 4 timestamps.
CREATE OR REPLACE FUNCTION date_overlap(start1 timestamp, end1 timestamp, start2 timestamp, end2 timestamp) RETURNS int STRICT AS '
-- STRICT returns NULL if any input is NULL
BEGIN
-- for valid ranges, end1 must be after (or equal to) start1, end2 must be after (or equal to) start2
-- for valid overlap, either end1 is after (or equal to) start2 or end2 is after (or equal to) start1
IF end1 < start1 OR end2 < start2 OR start1 < start2 AND end1 < start2 OR start2 < start1 AND end2 < start1 THEN
RETURN NULL;
ELSE
IF start1 > start2 THEN
IF end1 < end2 THEN
RETURN EXTRACT (EPOCH FROM end1 - start1) :: int/60;
ELSE
RETURN EXTRACT (EPOCH FROM end2 - start1) :: int/60;
END IF;
ELSE
IF end2 < end1 THEN
RETURN EXTRACT (EPOCH FROM end2 - start2) :: int/60;
ELSE
RETURN EXTRACT (EPOCH FROM end1 - start2) :: int/60;
END IF;
END IF;
END IF;
END;
' LANGUAGE 'plpgsql'
Usage:
SELECT date_overlap(timestamp1, timestamp2, timestamp3, timestamp4) FROM myTable
Returns: # mins as int
Upvotes: 5
Reputation: 14937
Assuming each date range consists of two date fields; first range: date1, date2, second range: date3, date4 and the first range ends after the second one begins.
SELECT (date3 - date2) * interval '1 minute' FROM myTable
Not tested, but should work.
Upvotes: 0