Group by Year, Month, or Day in PostgreSQL

PostgreSQL is one of the best relational databases that developers use on a daily basis.

In fact, PostgreSQL supports a wide range of data manipulation and analysis features. One of the most important analysis features is grouping data by a specific interval.

Let’s represent how PostgreSQL handles this feature elegantly.

Before digging deeper, let’s consider that you have the following orders table:

And your manager asks you three requirements:

1. Calculate the total sales every year.
2. Calculate the total sales every month in 2023.
3. Calculate the total sales every day in July 2023.

Let’s solve the first requirement.

This function, as its name implies, truncates a date based on a specific date part. You can know more about it here.

SELECT DATE_TRUNC('YEAR', "createdAt") AS year, SUM("price") AS total
FROM "orders"
GROUP BY year
ORDER BY year

This statement returns:

You might have noticed that the year field returned as a timestamp rounded to a specified level of precision, not as a number.

To return the year as a number, you can use the following:

SELECT DATE_PART('YEAR', DATE_TRUNC('YEAR', "createdAt")::TIMESTAMP)::INTEGER AS year
...

::INTEGER casts the double value returned from DATE_PART() to an integer.

This function extracts a year, month, or … from a date. Know more about it here.

SELECT EXTRACT(YEAR FROM "createdAt")::INTEGER AS year, SUM("price") AS total
FROM "orders"
GROUP BY year
ORDER BY year

This statement returns:

Unlike the DATE_TRUNC() function, the EXTRACT() function returns the year as a double. So I used ::INTEGER to cast the double value returned from EXTRACT() to an integer.

This function converts its input to a string. Check it out here.

SELECT TO_CHAR("createdAt", 'YYYY')::INTEGER AS year, SUM("price") AS total
FROM "orders"
GROUP BY year
ORDER BY year

::INTEGER casts the string value returned from TO_CHAR() to an integer.

This statement returns the same result as EXTRACT().

Let’s solve the second requirement.

Like grouping by year, you can group by month in a year using the same functions.

SELECT DATE_TRUNC('MONTH', "createdAt") AS month, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('YEAR', "createdAt") = 2023
GROUP BY month
ORDER BY month

This statement returns:

SELECT EXTRACT(MONTH FROM "createdAt")::INTEGER AS month, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('YEAR', "createdAt") = 2023
GROUP BY month
ORDER BY month

This statement returns:

SELECT TO_CHAR("createdAt", 'MM')::INTEGER AS month, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('YEAR', "createdAt") = 2023
GROUP BY month
ORDER BY month

This statement returns the same result as EXTRACT().

Let’s solve the third requirement.

SELECT DATE_TRUNC('DAY', "createdAt") AS day, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('MONTH', "createdAt") = 7 AND DATE_PART('YEAR', "createdAt") = 2023
GROUP BY day
ORDER BY day

This statement returns:

SELECT EXTRACT(DAY FROM "createdAt")::INTEGER AS day, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('MONTH', "createdAt") = 7 AND DATE_PART('YEAR', "createdAt") = 2023
GROUP BY day
ORDER BY day

This statement returns:

SELECT TO_CHAR("createdAt", 'DD')::INTEGER AS day, SUM("price") AS total
FROM "orders"
WHERE DATE_PART('MONTH', "createdAt") = 7 AND DATE_PART('YEAR', "createdAt") = 2023
GROUP BY day
ORDER BY day

This statement returns the same result as EXTRACT().

In this article, we learned how to group data by a specific date interval, year, month, and day.

And to do so, we introduced three different ways:

• DATE_TRUNC()
• EXTRACT()
• TO_CHAR()

If you enjoyed this article, I’d truly appreciate it if you could share it—it really motivates me to keep creating more helpful content!

If you’re interested in exploring more, check out these articles.

• Group by Year, Month, or Day in MySQL
• MongoDB GridFS Made simple
• Isolation Levels In SQL Server With Examples
• 4 Ways To Handle Asynchronous JavaScript

Thanks for sticking with me until the end—I hope you found this article valuable and enjoyable!