Postgres SQL: getting one result per date when the start and end date column

Question

This is a bit like Getting a list of dates in a range in PostgreSQL , but not quite the same (I think).

I have an SQL table in which each row contains a start and end date. I would like to formulate a request so that for each row, for each date within 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 understand that in this result set the unique and primary key lines are duplicated, but for me it does not matter. Any ideas would be very welcome!

Answer 1

You can join generate_series as easily as you selected 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 (sd between t.start and t.end) 

Of course, you should indicate real dates, not 2010-01-01 and 2013-01-01 .

Older versions, such as 8.3, do not have generate_series , which works with timestamps, but you can fake it using generate_series to create daily offsets from the start 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, configure 0 , 1096 and '2010-01-01' according to your data.