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:
Calculate the total orders' sales every year.
Calculate the total orders' sales every month in 2023.
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:
| 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
...
::INTEGERcasts the double value returned fromDATE_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:
| 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
::INTEGERcasts the string value returned fromTO_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:
| 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
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
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:
| 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
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
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)