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

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)
Code language: SQL (Structured Query Language) (sql)

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)
Code language: PHP (php)

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)
Code language: PHP (php)

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' );
Code language: PHP (php)

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)
Code language: PHP (php)

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)
Code language: PHP (php)

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;
Code language: JavaScript (javascript)

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)
Code language: CSS (css)

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:

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;
Code language: JavaScript (javascript)

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)
Code language: PHP (php)

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
Code language: JavaScript (javascript)

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
Code language: JavaScript (javascript)

Boom. Works great:

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

March 17, 2022

Importing data into PostgreSQL can be time consuming and painful – unless you toss the GUI tools and use scripts.

Importing a CSV Into PostgreSQL Like a Pro

April 17, 2020

This is part 3 of a series of posts I’m writing for Friendo, a web person who wants to get their hands a lot dirtier with Node and Postgres.

Postgres For Those Who Can’t Even, Part 3 – In The Real World

February 5, 2020

Getting up to speed with Postgres and Node can be daunting but in this post I’ll dive into how you can easily work with both – including JSON document storage… in Postgres!

Postgres For Those Who Can’t Even, Part 2 – Working with Node and JSON