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.
