Postgres SQL: Getting one result per date when table has start and end date columns

This is a little similar to Getting date list in a range in PostgreSQL, but not exactly the same (I think).

I have a SQL table where every row contains a start and end date. I would like to form a query so that for each row, for each date inside this range, I get one result.

For example: id=1, title=MyRange, start=2012-08-05, end=2012-08-08

Query: SELECT * FROM myTyble where id=1 AND ...

Result:

 id=1, title=MyRange, date=2012-08-05
 id=1, title=MyRange, date=2012-08-06
 id=1, title=MyRange, date=2012-08-07
 id=1, title=MyRange, date=2012-08-08

I realize that unique and primary key rows will be duplicated in this result set, but that does not matter for me. Any ideas would be very welcome!

Answers

You can join to generate_series just as easily as you select from it. Something like this should do the trick:

select t.*
from generate_series('2010-01-01'::timestamp, '2013-01-01'::timestamp, '1 day') as s(d)
join your_table t on (s.d between t.start and t.end)

You'd supply the real dates you want instead of 2010-01-01 and 2013-01-01 of course.

Older versions, such 8.3, don't have a generate_series that works with timestamps but you can fake it by using generate_series to produce day offsets from an initial date:

select t.*
from generate_series(0, 1096) dt(d)
join your_table t on ('2010-01-01'::date + dt.d between t.start and t.end)

As before, adjust the 0, 1096, and '2010-01-01' to match your data.

Need Your Help