Search code examples
sqlpostgresqlgenerate-series

Generate Series from Rows in PostgreSQL

I have a table of reservations which has two columns (started_at, and ended_at). I want to build a query that expands reservation rows into their individual days. So for instance if a reservation lasted 5 days I want 5 rows back for it. Something along the lines of:

Current Output

id | started_at | ended_at
----------------------------
1  | 2016-01-01 | 2016-01-05
2  | 2016-01-06 | 2016-01-10

Desired Output

id | date
---------------
1  | 2016-01-01
1  | 2016-01-02
1  | 2016-01-03
1  | 2016-01-04
1  | 2016-01-05
2  | 2016-01-06
2  | 2016-01-07
2  | 2016-01-08
2  | 2016-01-09
2  | 2016-01-10

I figured that generate_series might be of use here but I'm not certain of the syntax. Any help is greatly appreciated

SQL Fiddle

http://sqlfiddle.com/#!15/f0135/1

Solution

  • This runs ok on your fiddle 

    SELECT id, to_char(generate_series(started_at, ended_at, '1 day'),'YYYY-MM-DD') as date
FROM reservations;