The GROUP BY clause in Postgres allows us to group the table’s data based on specific column(s), making it easy to analyze and understand relationships and patterns within your data. In Postgres, you can use the EXTRACT(), DATE_TRUNC(), and DATE_PART() function to extract the month from a date field and then use the GROUP BY clause to group the results by month.

This blog will show you how to group the table’s data by month in Postgres. In this regard, the below-listed topics will be covered in this Postgres blog:

• Sample Table
• How to Group by Month in Postgres Using EXTRACT() Function?
• How to Group by Month in Postgres Using DATE_TRUNC() Function?
• How to Group by Month in Postgres Using DATE_PART() Function?

So, let’s start!

Sample Table

We have already created a sample table named “article_details”, whose content is shown in the below snippet:

The “article_details” table has three columns: article_id, article_title, and published_date. This post will show you how to group the table’s data by month in Postgres.

How to Group by Month in Postgres Using EXTRACT() Function?

In Postgres, you can use the EXTRACT() function to extract a month from the specified column and then use the GROUP BY clause to group the table’s data by date:

SELECT EXTRACT('MONTH' FROM published_date) AS published_month,
COUNT(article_id) AS article_count
FROM article_details
GROUP BY EXTRACT('MONTH' FROM published_date);

The output shows that the table’s data has been grouped by month successfully.

How to Group by Month in Postgres Using DATE_TRUNC() Function?

The DATE_TRUNC() function, along with the GROUP BY clause, is used in Postgres to group the table’s data by month:

SELECT DATE_TRUNC('MONTH', published_date) AS published_month,
COUNT(article_id) AS count
FROM article_details
GROUP BY DATE_TRUNC('MONTH', published_date);

The DATE_TRUNC() function will truncate all the fields after the month to their initials(such as time will be initialized with 0, and days will be initialized with 1):

The output verifies that the table’s data has been grouped by month successfully.

How to Group by Month in Postgres Using DATE_PART() Function?

The DATE_PART() function in Postgres lets you extract a month from a column and then group the data by date using the GROUP BY clause:

SELECT DATE_PART('MONTH', published_date) AS published_month,
COUNT(article_id) AS count
FROM article_details
GROUP BY DATE_PART('MONTH', published_date);

The output snippet shows that the table’s data has been grouped by month successfully.

That’s it from this Postgres guide!

Conclusion

In Postgres, the EXTRACT(), DATE_TRUNC(), and DATE_PART() functions are used to extract the month from a date field and then use the GROUP BY clause to group the results by month. For this purpose, specify the “MONTH” as the first argument to any of the functions mentioned above and then use the GROUP BY clause to group the table’s data by month. This Postgres blog explained how to group the table’s data by month using different methods.