Group by Year, Month, or Day in PostgreSQL
Tags: database, sql, postgres, analytics
Published: 2023-06-26 10:36:30

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](https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-date_trunc/).

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

```sql
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](https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-extract/).

```sql
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](https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-to_char/).

```sql
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()

```sql
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()
```sql
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()

```sql
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()

```sql
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()

```sql
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()

```sql
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**](https://mayallo.com/mongodb-gridfs/)
    
*   [**Isolation Levels In SQL Server With Examples**](https://mayallo.com/isolation-levels-sql-server/)
    
*   [**4 Ways To Handle Asynchronous JavaScript**](https://mayallo.com/asynchronous-javascript/)
    

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