SQL 中两个日期范围的重叠（以分钟为单位）

Question

有没有一种简单的方法可以获取 SQL 中两个日期范围的重叠分钟数？我正在使用 PostgreSQL。

Answer 1

该函数将处理 4 个时间戳的任何输入。

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'

用法：

SELECT date_overlap(timestamp1, timestamp2, timestamp3, timestamp4) FROM myTable

返回：# mins as int

Answer 2

假设每个日期范围由两个日期字段组成；第一个范围：date1、date2，第二个范围：date3、date4，第一个范围在第二个范围开始后结束。

SELECT (date3 - date2) * interval '1 minute' FROM myTable

未经测试，但应该可以工作。

Answer 3

**

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

**