## DEV Community

Mohamed Mayallo

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

# 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.

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

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:

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
...
``````

`::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:

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
``````

`::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:

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.