Grouping data by date and time is a common task while working with databases. Grouping the table’s data by date/time intervals (such as months, weeks, days, hours, etc.) assists us in gaining valuable insights and trends in our data. PostgreSQL offers various built-in functions to group data by time, such as the DATE_TRUNC(), EXTRACT(), and DATE_PART() functions.

This blog post will explain several methods to group the table’s data by time in Postgres. So, let’s get started.

How to Group by Time in Postgres Via DATE_TRUNC() Function?

The most convenient method to group table data is by using the DATE_TRUNC() function, which allows us to truncate a timestamp to a specific level of precision, such as the month, day, hour, etc. The following snippet shows the content of a sample table named "article_info":

Let’s group the table’s data by “Year” via the DATE_TRUNC() function:

SELECT DATE_TRUNC('YEAR', publish_date) published_year,
COUNT(article_id) AS count
FROM articles_info
GROUP BY DATE_TRUNC('YEAR', publish_date);

The output proves that the DATE_TRUNC() function groups the table’s data by year.

Note: Similarly, you can group data by month, day, hours, etc., using the DATE_TRUNC() function.

How to Group by Time in Postgres Via DATE_PART() Function?

The DATE_PART() function can also be used to group the data by date/time. This function allows us to extract a date part and group the records by date/time using the GROUP BY clause. Let’s group the table’s data by “DAY” via the DATE_TRUNC() function:

SELECT DATE_PART('DAY', publish_date) day_of_month,
COUNT(article_id) AS count
FROM articles_info
GROUP BY DATE_PART('DAY', publish_date);

The output verifies that the DATE_PART() function groups the table’s data by day.

How to Group by Time in Postgres Via EXTRACT() Function?

Another option to group table data by time is to use the built-in EXTRACT() function, which allows you to extract a specific part of a timestamp, such as a year, a week, an hour, etc. The following snippet shows the content of a sample table named "emp_attendence":

Let’s group the table’s data by “minutes” via the EXTRACT() function:

SELECT EXTRACT('MINUTES' FROM emp_check_in) AS minutes,
COUNT(emp_id)
FROM emp_attendence
GROUP BY EXTRACT('MINUTES' FROM emp_check_in);

This way, you can use the EXTRACT() function to group the table’s data by “minutes”.

That’s it from this post.

Conclusion

PostgreSQL offers various built-in functions to group data by time, such as the DATE_TRUNC(), EXTRACT(), and DATE_PART() functions. The most convenient method to group table data is the DATE_TRUNC() function, which allows us to truncate a timestamp to a specific level of precision, such as the month, day, hour, etc. This blog post explained how to group the data by date/time in PostgreSQL.