Aggregate continuous ranges of dates
SELECT min(reserved_date) AS start_date , max(reserved_date) AS end_dateFROM ( SELECT reserved_date , reserved_date - row_number() OVER (ORDER BY reserved_date)::int AS grp FROM reserved_dates ) sub GROUP BY grpORDER BY grp;Compute a running, gap-less number in chronological order with the window function
row_number()- unless yourreserved_days_idhappens to be gap-less and in chronological order, which is typically not the case.Deduct that from
reserved_datein each row (after converting tointeger). Consecutive days end up with the same date valuegrp- which has no other purpose or meaning than to form groups.Aggregate in the outer query. Voilá.
Similar cases: