DEV Community

Cover image for Group by Year, Month, or Day in PostgreSQL
Mohamed Mayallo
Mohamed Mayallo

Posted on • Edited on • Originally published at mayallo.com

Group by Year, Month, or Day in PostgreSQL

#database #sql #postgres #analytics

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:

id price createdAt
1 100 2022-01-12 01:15:00
2 120 2022-02-10 23:44:00
3 80 2023-01-09 08:14:00
4 150 2023-07-09 20:50:00
5 50 2023-07-10 09:33:00
6 30 2023-07-10 15:23:00
7 90 2023-09-23 10:12:00

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
Enter fullscreen mode Exit fullscreen mode

This statement returns:

year total
2022-01-01 00:00:00 220
2023-01-01 00:00:00 400

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 ...
Enter fullscreen mode Exit fullscreen mode

::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
Enter fullscreen mode Exit fullscreen mode

This statement returns:

year total
2022 220
2023 400

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
Enter fullscreen mode Exit fullscreen mode

::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
Enter fullscreen mode Exit fullscreen mode

This statement returns:

month total
2023-01-01 00:00:00 80
2023-07-01 00:00:00 230
2023-09-01 00:00:00 90

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
Enter fullscreen mode Exit fullscreen mode

This statement returns:

month total
1 80
7 230
9 90

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

This statement returns:

day total
2023-07-09 00:00:00 150
2023-07-10 00:00:00 80

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
Enter fullscreen mode Exit fullscreen mode

This statement returns:

day total
9 150
10 80

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
Enter fullscreen mode Exit fullscreen mode

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:

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

Top comments (0)