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.

ClojureScript: Writing a Javascript Library with ClojureScript and OM / Starting Point Of A Sunburst Chart Library

Hackerthon - The Starting Point 

Some weeks ago i participated on our one day company hackerthon. This means that time is a very limited resource and fast solutions are welcome. I joined a group to write a wrapper for a profiler which uses statistical analysis of stacktraces to find out where the application spend the most of it's processing time. Our team consists of 4 backend developer (including me) and one ops.  To display the results in a nice way we decided to use a sunburst chart. Because frontend developers where rare :-) i decided to take over this part. I also was interested to get more knowledge in this sector. 

I tried to find a sunburst chart which fits these requirements:

• Written to use in a web app - on client side
• Open Source  - in case that we need more control over the visualisation
• should be dynamic & updateable - we like to display real time data 

As i mentioned in the beginning time was rare and i did only a quick evaluation. The main goal was to find a sunburst chart which can be used and integrated in our project in only a few hours.

There are some libraries which have a sunburst chart as part of the library or let you handcraft a sunburst chart which the given components. But in both cases it needs some time learn how to use the library. This is nearly impossible during a one day hackerthon.

Here is an example chart created with the D3 library. You can check the code and the sample data of the sunburst chart here. 

The code example shows that without any knowledge of D3 the creation of a sunburst chart is not a simple task. 

The chart also lacks labels which describe the single segments of the chart. These can be added as described in Martin Walters Post. The image on the right shows the result and also the difficulties of arranging labels in a sunburst chart. 

I personally like more an arrangement of the labels when these are wound around the center. But i haven't found a free / open source library with these kind of arrangement. That was the starting point for me to think about writing my own sunburst chart library.

Sunburst Chart generated with the Carrot Search Circles Library.

In the end we found the commercial Carrot Search Circles which displays the labels in way we liked and we used this for our Hackerthon.

After one day of a lot of fun we where all very exited about the result that we archived and i decided to do more frontend development in the future. My first project in that direction should be my own implementation of a sunburst chart. 

Read more in my next post why i like to use ClojureScript and Om for writing my first JavaScript/ClojureScript library.