Group by Year, Month, or Day in PostgreSQL

Introduction

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.

Your Manager Requirements

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

And your manager asks you three requirements:

1. Calculate the total orders' sales every year.

2. Calculate the total orders' sales every month in 2023.

3. Calculate the total orders' sales every day in July 2023.

Group by Year

Let's solve the first requirement.

1- Using DATE_TRUNC()

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 note, 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.

2- Using EXTRACT()

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.

3- Using TO_CHAR()

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().

Group by Month in a Year

Let's solve the second requirement.

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

1- Using DATE_TRUNC()

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:

2- Using EXTRACT()

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:

3- Using TO_CHAR()

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().

Group by Day in a Month of a Year

Let's solve the third requirement.

1- Using DATE_TRUNC()

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:

2- Using EXTRACT()

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:

3- Using TO_CHAR()

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().

Conclusion

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

And to do so, we introduced three different ways using:

• DATE_TRUNC()

• EXTRACT()

• TO_CHAR()

Before you leave

If you found this article useful, check out these articles as well:

• MongoDB GridFS Made simple

• Isolation Levels In SQL Server With Examples

• 4 Ways To Handle Asynchronous JavaScript

Thanks a lot for staying with me up till this point. I hope you enjoy reading this article.