PostgreSQL allows you to group by day. This option is handy when you want to group the table elements based on the day when you have a date column. The GROUP BY clause works with the PostgreSQL select statement.
This guide presents different examples on how to group by date on Postgres. With these examples, you will understand how the group by day (date) works. Let’s begin!
How to Group by Day (Date) on PostgreSQL
The first thing is to understand the syntax that is required to group by day. The syntax that you use depends on the date function that you want to execute. For instance, use the following syntax using the DATE_TRUNC function:
The DATE_TRUNC function allows you to convert the date in the date_column per day. Let’s use the following table for our examples:
Example 1: Group by Day from the Table
When you want to group the elements on a per-day basis, you can select the day using the date_trunc function. You then must group by day, as illustrated in the following command:
The command eliminates the duplicate dates in the table. You can note that only six rows are returned instead of seven rows in the table.
The previous example only selected one column. However, you can include different columns from your table for clarity. Any column that you select must be specified in the GROUP BY clause. You can also add what name to use for the group by output of the column that you select.
For instance, we specify to display our date column as “dates”.
Example 2: Use the Group by Day (Date) with Aggregate Functions
The GROUP BY clause can be applied with different aggregate functions for a more specific output. In this example, we use the COUNT() to calculate the number of instances where we have the same dates when grouping the rows per day basis.
Note how and where we apply the aggregate function. It comes immediately after selecting the target column which we want to use with the GROUP BY clause.
The output confirms that we have two similar dates based on the count column.
Example 3: Using the TO_CHAR Function
When grouping the dates per day, you can also get the particular day of the week of each date in your table using the TO_CHAR function.
The following command returns the days of the week which are associated with each date in the specific column:
The TO_CHAR function takes the date column as its first argument. Reversing the arrangement raises an error like the following:
Example 4: Using the Group by Day with Other Clauses
Combining other clauses when you want better output when grouping the elements per day basis is possible. This example uses the DATE_TRUNC function with the ORDER BY clause to order the date in descending order:
Suppose you want to group by day to find the urgency of the elements in your table. This example is an excellent illustration of achieving that.
Example 5: Using the DATE_PART with the Group by Day
When grouping the elements per day, it’s possible to extract the particular month date from the date column. When you have a date value, the DATE_PART function lets you extract the month’s date as illustrated in the following example.
You must still specify the “day” as an argument when using the function to ensure that the grouping is on a per-day basis.
You can combine the DATE_PART function with the TO_CHAR function to extract the date of the month and the day of the week. Here’s a classic example on how the command for that looks like:
Conclusion
You can group the dates per day using the DATE_TRUNC and the DATE_PART functions. This post presented various examples to guide you on how to group by day when working with PostgreSQL. Hopefully, you gained a foundation and can scale your experience in working with the PostgreSQL group by day (date).
