How to Group by Month in PostgreSQL

Problem:

You'd like to group records by month in a PostgreSQL database.

Example:

Our database has a table named watch with data in the columns id, name, and production_timestamp.

Solution:

You can use the DATE_TRUNC() function to group records in a table by month.

Here's the query you would write:

SELECT 
  DATE_TRUNC('month', production_timestamp) AS production_to_month,
  COUNT(id) AS count 
FROM watch
GROUP BY DATE_TRUNC('month', production_timestamp);

Here's the result of the query:

Discussion:

Use the DATE_TRUNC() function if you want to retrieve a date or time with a specific precision from a PostgreSQL database. (In our example, we used month precision.)

This function takes two arguments. First, we have the date part specifier (in our example, 'month'). Note that the specifier is a string and needs to be enclosed in quotes.

The second argument is the timestamp value; this can be an expression returning a timestamp value or the name of a timestamp column. (In our example, we use the column production_timestamp).

The function DATE_TRUNC() truncates the timestamp to the given precision (in this case, the month). In our database, the smartwatch has the production date 2019-09-15 07:35:13; after it's been truncated to month precision, it becomes 2019-09-01 00:00:00. The month-level precision causes the day to be displayed as '01' and the time to be filled with zeros. (The truncated date parts (e.g. days, months) of the timestamp are replaced with ones.)

Grouping records by month is very common in PostgreSQL. In our example, the number of products is totaled for each month. (The COUNT() aggregate function counts the number of products). If you group records using an aggregate function, you have to use the GROUP BY clause. After the GROUP BY clause, you should place the columns or expressions used with the aggregate function in the SELECT statement. (In our example, this is DATE_TRUNC('month', production_timestamp).)

Of course, you don't have to use the DATE_TRUNC() function just to group records. You can also use it to get the first day of the month for a given date.

For each watch, let's get the name and production date to month precision – no grouping needed. Here's the query you would write:

SELECT 
  name,
  DATE_TRUNC('month', production_timestamp) AS production_to_month
FROM watch;

Here's the result:

In this query, the function DATE_TRUNC() truncates the timestamp to month precision. In our database, the smartwatch has the production date '2019-09-15 07:35:13'; now it's '2019-09-01 00:00:00', which is the first day of the month.