Generating series with Postgres

Social websites usually have a feature that displays the users engagement with a site or a log of a particular activity over time.

Here's Microsoft and former StackOverflow champion, Nick Craver's commit streak as an example.

Or something like CrossOutCalendar

Example

For our example, we'll assume user's logging their weights after each workout in a time-series. As an additional scenario we'll assume that users can log their weights as many times in a day as they want.

Goal

What we want is to be able to display a monthly calendar with the weights of the user on the days it was logged and highlight the days where the user didn't log anything.

Here's what our dataset could look like.

generate_series with Postgres

generate_series allows us to generate a series for a specific type. For example, we can generate a series on integers by passing a range

Or the same thing with a date range

So our query looks something like this. We use a few common table expressions to simply the process of combining the date series with the dates with weight logged.

To obtain a single logged weight for a day we average out multiple logs for a given day.

And we end up with a table like this.