Check if contiguous date interval exists

6 votes

2 answers

8382 views

postgresql window-functions gaps-and-islands

                          I have a table in PostgreSQL which describes some events that have a start date and an end date: 

    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
    )

A single event may overlap the previous and the next one. In the following table the first 3 of 4 events form a contiguous time interval:

    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'

Is it possible to write a query that checks if a contiguous date interval between two given dates exists?

I would like to have something like:

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

Asked by hank (919 rep)

Apr 3, 2015, 12:06 PM
Last activity: Jul 14, 2023, 03:44 PM

Check if contiguous date interval exists

Related Questions