postgresql - 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