Postgres/PSQL: Generate day based statistic for accumulated data during a time range - given only the start and end date

The Problem

I would like to have a statistic how much campaigns where running on one day over a specified time range. But i had only the start and the end date of a campaign stored in my postgres database. This images show an example which is used for this article.

The box with the S inside represents the start date, the box with the E inside the end date.

Example Data

Here are the entries in the database. You can see that the campaign table contains five campaigns. The two of them named with Out1 are not in the queried time range. The others fit the time ranges from the example above.

The Solution

Here is a psql query to get the number of campaign between the 2014-06-01 and 2014-06-30. The date range is inclusive the start and end date.

1 2 3 4 5 6 7

SELECT time_range.d AS "Date", count(time_range.d) AS "Number Of Campaings" FROM generate_series ('2014-06-01'::timestamp, '2014-06-30'::TIMESTAMP, '1 d'::INTERVAL) as time_range(d) JOIN campaign c ON (time_range.d BETWEEN c.start_date AND c.end_date) GROUP BY time_range.d ORDER BY time_range.d;

Result

The result after executing the query returns the expected "Number Of Campaigns" for the time range in the example. You can see that only dates are returned which have at least one campaign online.