Aggregate continuous ranges of dates

Question

Let's say you have the following PostgreSQL sparse table listing reservation dates:

CREATE TABLE reserved_dates (
    reserved_days_id    SERIAL  NOT NULL,
    reserved_date       DATE    NOT NULL
);

INSERT INTO reserved_dates (reserved_date) VALUES
    ('2014-10-11'),
    ('2014-10-12'),
    ('2014-10-13'),
    -- gap
    ('2014-10-15'),
    ('2014-10-16'),
    -- gap
    ('2014-10-18'),
    -- gap
    ('2014-10-20'),
    ('2014-10-21');

How do you aggregate those dates into continuous date ranges (ranges without gaps)? Such as:

 start_date | end_date
------------+------------
 2014-10-11 | 2014-10-13
 2014-10-15 | 2014-10-16
 2014-10-18 | 2014-10-18
 2014-10-20 | 2014-10-21

This is what I came up with so far, but I can only get start_date this way:

WITH reserved_date_ranges AS (
    SELECT reserved_date,
           reserved_date
           - LAG(reserved_date) OVER (ORDER BY reserved_date) AS difference
    FROM reserved_dates
)
SELECT *
FROM reserved_date_ranges
WHERE difference > 1 OR difference IS NULL;

Answer 1

SELECT min(reserved_date) AS start_date
     , max(reserved_date) AS end_date
FROM  (
   SELECT reserved_date
        , reserved_date - row_number() OVER (ORDER BY reserved_date)::int AS grp
   FROM   reserved_dates
   ) sub 
GROUP  BY grp
ORDER  BY grp;

1. Compute a running, gap-less number in chronological order with the window function row_number() - unless your reserved_days_id happens to be gap-less and in chronological order, which is typically not the case.

2. Deduct that from reserved_date in each row (after converting to integer). Consecutive days end up with the same date value grp - which has no other purpose or meaning than to form groups.

3. Aggregate in the outer query. Voilá.

SQL Fiddle.

Similar cases:

• Rank based on sequence of dates
• Group by repeating attribute