4

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!

Community
  • 1
  • 1
Jens
  • 1,386
  • 14
  • 31

1 Answers1

5

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • This looks neat (I'd have to benchmark it first, of course) but I get an error using `generate_series` for my Postgres 8.3 installation. Do I need any extra options, extensions or libraries? I get "ERROR: function generate_series(timestamp without time zone, timestamp without time zone, interval) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts." – Jens Sep 22 '12 at 20:46
  • @Jens: 8.3's [`generate_series`](http://www.postgresql.org/docs/8.3/interactive/functions-srf.html#FUNCTIONS-SRF-SERIES) doesn't have a timestamp version but you can fake it as in my update. – mu is too short Sep 22 '12 at 21:00