Simple Monthly Reports in PostgreSQL

I have a reporting backend for my book/video business that has one chart which I stare at every day: the daily sales:

I use Google Analytics religiously, but it's not reliable for ecommerce because ad blockers will also block Google Analytics so a number of sales simply aren't recorded.

Anyway: I need to roll my own reporting if I want to see anything of substance, which is fine as love playing with PostgreSQL. When you do that, however, you run into some interesting problems. Such as this one:

Today is the first day of the month, so the chart only has a single value and the formatting is completely off. In fact it's off every day! This has been bugging me for a while, so today I decided to fix that.

Generating a Series of Dates

The problem is straightforward: I need to see all the days in a given month. PostgreSQL has extensive date functions, but nothing (that I've seen) that will just spit out the dates in a given month.

To get around this, I'll rely on an old friend: generate_series.

There's no surprise with this function, it does what you might expect, creating a logical series from a seed and bound:

rob=# select * from generate_series(1,10);
  generate_series

           1
           2
           3
           4
           5
           6
           7
           8
           9
          10
(10 rows)

You can also add a step with a third argument:

rob=# select * from generate_series(1,10,2);
 generate_series 

           1
           3
           5
           7
           9
(5 rows)

This is where things get usefully mindblowing: it also works with dates and intervals:

rob=# select * from generate_series(now(), now() + '5 days', '1 day');
generate_series-------------------------------
 2018-08-01 14:10:52.380404-07
 2018-08-02 14:10:52.380404-07
 2018-08-03 14:10:52.380404-07
 2018-08-04 14:10:52.380404-07
 2018-08-05 14:10:52.380404-07
 2018-08-06 14:10:52.380404-07
(6 rows)

Interval syntax is one of the things I absolutely love about working with PostgreSQL and dates. I know that many people don't like arbitrary strings to represent something, but I think you can probably get over that with the obvious "1 day" syntax, don't you think?

Generating a Series of Days Within a Month

The easiest thing to do is to pass in dates for the start and end of the month:

select * from generate_series(
'2018-08-01'::timestamptz,
'2018-08-31'::timestamptz,
'1 day'
);

That works as expected, but it's cumbersome. This is where PostgreSQL can help us with some date functions. What I need is to "round down" the month to day one, and I can do that using a date_trunc, which truncates a date to a specified precision:

rob=# select date_trunc('month',now());
   date_trunc------------------------
 2018-08-01 00:00:00-07
(1 row)

I can use this same trick to get the last day of the month, using interval syntax:

rob=# select date_trunc('month',now()) + '1 month'::interval - '1 day'::interval as end_of_month;
  end_of_month------------------------
 2018-08-31 00:00:00-07
(1 row)

That looks nuts, doesn't it? Here's what's happening:

the date_trunc function is returning a timestamp with time zone (or timestamptz)
I am then incrementing that timestamptz, which is 2018-08-01 00:00:00-07 by a month, making it 2018-09-01 00:00:00-07
I don't want the start of September, I want a single day before that, so I decrement it by a day using - '1 day'

That's that. I can now plug this into generate_series:

select * from generate_series(
date_trunc('month',now()),
date_trunc('month',now()) + '1 month' - '1 day'::interval,
'1 day'
) as dates_this_month;

Which returns every date, in order:

...

2018-08-25 00:00:00-07 2018-08-26 00:00:00-07 2018-08-27 00:00:00-07 2018-08-28 00:00:00-07 2018-08-29 00:00:00-07 2018-08-30 00:00:00-07 2018-08-31 00:00:00-07 (31 rows)

Turning Our Date Range Into a Usable Table

I could plug this SQL into a bigger query and use it straight away, but it's way too useful for that. Let's wrap it with a function, shall we? That way we can pass in whatever date or month we want to use:

-- this is 
create function dates_in_month(the_date timestamptz=now())
returns table(the_date date) as $
select d::date from generate_series(
date_trunc('month',the_date),
date_trunc('month',the_date) + '1 month' - '1 day'::interval,
'1 day') as series(d);
$
language sql;

A few things to note:

I'm defaulting the_date parameter to today's date for convenience
You can send in any date, and the month of that date will be used in the function
I'm casting the series return as a date because that's what it is; a timestamptz here is useless
To cast that, I need to alias the function to explicitly return it's inline value (d)

This works great:

rob=# select * from dates_in_month();
  the_date  

 2018-08-01
 2018-08-02
 2018-08-03
 ...
 2018-08-28
 2018-08-29
 2018-08-30
 2018-08-31
(31 rows)

Now I just need to use it in a sales query.

Joining Things Together To Produce The Chart

I have a view in my database called sales_fact that sums up the order totals, their count, and expresses the dates in a number of ways. Here it is:

create view sales_fact as 
  select sum(total) as sales, 
  count(1) as sales_count,
  created_at::date as sales_date,
  date_part('year',created_at at time zone 'hst') as year,
  date_part('quarter',created_at at time zone 'hst') as quarter,
  date_part('month',created_at at time zone 'hst') as month,
  date_part('day',created_at at time zone 'hst') as day
from orders
group by orders.created_at
order by orders.created_at

I want to join those numbers to my date series so I can have every day represented in my chart, not just a fat blue blob. To do that, I can use a simple left join:

select 
  the_date, 
  sum(sales) as sales, 
  sum(sales_count) as sales_count
from days_in_month()
left join sales_fact on the_date = sales_fact.sales_date
group by days_in_month.the_date

Boom. Works great:

PostgreSQL is a joy to work with, and solutions to common problems are often right around the corner.