檢查是否存在連續的日期間隔

我在 PostgreSQL 中有一個表，它描述了一些具有開始日期和結束日期的事件：

CREATE TABLE my_table
(
 event_id serial NOT NULL,
 start_date timestamp without time zone NOT NULL,
 end_date timestamp without time zone NOT NULL
)

單個事件可能與前一個事件和下一個事件重疊。在下表中，4 個事件中的前 3 個形成一個連續的時間間隔：

1   '2015-04-02 22:09:03'   '2015-04-02 22:19:05'
2   '2015-04-02 22:17:38'   '2015-04-02 22:27:38'
3   '2015-04-02 22:25:21'   '2015-04-02 22:36:23'
4   '2015-04-02 22:45:23'   '2015-04-02 22:55:23'

是否可以編寫一個查詢來檢查兩個給定日期之間是否存在連續的日期間隔？

我想要類似的東西：

select ...
from my_table
where start_date > '2015-04-02' and end_date < '2015-04-06'

首先，我們結合重疊的區間來找到區間的所有連續“島”：

with c as
 ( select *, max(end_date) over (order by start_date
                                 rows between unbounded preceding
                                          and 1 preceding)
               as previous_max
   from my_table
 )
select start_date, 
      coalesce(lead(previous_max) over (order by start_date),
               (select max(end_date) from my_table)
              ) as end_date
from c 
where previous_max < start_date 
  or previous_max is null ;

之後，很容易檢查給定的間隔是否完全被找到的連續島嶼之一包圍。

  with c as
 ( select *, max(end_date) over (order by start_date
                                 rows between unbounded preceding
                                          and 1 preceding)
               as previous_max
   from my_table
 ) ,
cont as
 ( select start_date, 
          coalesce(lead(previous_max) over (order by start_date),
                   (select max(end_date) from my_table)
                  ) as end_date
   from c 
   where previous_max < start_date 
      or previous_max is null  
  )
select *
from cont
where tsrange(start_date, end_date)
      @>                             -- contains
     tsrange('2015-04-02 22:10:00', '2015-04-02 22:30:00') 
limit 1 ;

在SQLfiddle進行測試

引用自：https://dba.stackexchange.com/questions/96974