Postgresql – Query result in a daily date from a date range selection Postgres 9.3

dateformatpostgresql

I need to make a report that will output a result in a daily row result based on the datarange selected.

Example:

Table: sales
->id
->type
->gross
->created_at
->updated_at

For example my query is

SELECT COUNT(case when type = 'Completed Sale' then id end) as complete, 
       COUNT(case when disposition = 'Partial Sale' then id end) as partial, 
       SUM(gross)as revenue 
FROM sales 
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32';

The range is Sept 20 to Sept 24

So basically the output will be like:

complete | partial | revenue
1          1         2.3

What I want the output to be like this

start date | end date    | complete | partial | revenue
___________|_____________|__________|_________|____________
2015-09-20 | 2015-09-21  | 1        | 1       | 2.3
2015-09-21 | 2015-09-22  | 3        | 4       | 4.3
2015-09-22 | 2015-09-23  | 3        | 4       | 4.3
2015-09-23 | 2015-09-24  | 4        | 4       | 3.3

So as you can see, the output is the daily basis of the start date and end date. If choose 1 month range then there will be 30 or 31. Is that possible?

Best Answer

All that you have to add is the day (be it the start or the end date). This can be obtained using a cast to date, for example. When you add it to the query, you'll need a GROUP BY clause, too, on this expression - that will produce you the daily aggregates.

The whole query will look like

SELECT created_at::date AS start_date,
       created_at::date + 1 AS end_date,
       COUNT(case when type = 'Completed Sale' then id end) AS complete, 
       COUNT(case when disposition = 'Partial Sale' then id end) AS partial, 
       SUM(gross) AS revenue 
FROM sales 
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32'
GROUP BY created_at::date;

Notes:

  • using start_date and end_date like this can be slightly confusing - in normal life one expects the end date to be inclusive. With the way you use it might not be clear which data belong to which day - I would simply omit the end date.
  • in the GROUP BY clause it's enough to mention the cast expression only once - adding one day in another columns does not result in a different grouping.
Related Question