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

NHibernate Session Management Advice

c# winforms nhibernate session unitofworkapplication

I need some advice on NHibernate Session Management for a C# WinForms application.

c# add progress bar into textBox

c# .net visual-studio progress-bar

how can i do for add an progressBar into textBox area? ( something like safari browser do on its address bar ) ? Any tutorial?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.