CODEWITHSUNDEEP
postgresqldaterangeoverlap
Philip
Philip

Reputation: 338

Overlap of two date ranges in minutes in SQL

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

Answers (3)

Uthirasamy
Uthirasamy

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

ic3b3rg
ic3b3rg

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

ic3b3rg
ic3b3rg

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

Related Questions