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

Question

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!

mu is too short · Accepted Answer · 2012-09-22 20:59:58Z

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.

edited Sep 22, 2012 at 20:59

answered Sep 22, 2012 at 6:47

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jens Over a year ago

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."

mu is too short Over a year ago

@Jens: 8.3's generate_series doesn't have a timestamp version but you can fake it as in my update.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related