Timestamps are crucial in databases, and Postgres is no exception, but sometimes you may want to truncate them to a certain level of precision. Especially while grouping data by date or time. For this purpose, you must use the DATE_TRUNC() function to truncate timestamps to the desired level of precision.

This blog presents a detailed guide on rounding a timestamp in Postgres. So, let’s start!

How to Round Timestamps in PostgreSQL?

In PostgreSQL, the DATE_TRUNC() function is a convenient way for truncating timestamps. The DATE_TRUNC() function enables you to specify the timestamp field as well as the unit of time to which you want to truncate. For instance, by specifying the 'year' as an argument, you can truncate a timestamp to the nearest year.

Syntax

The snippet provided below shows how to use the DATE_TRUNC() function in Postgres:

DATE_TRUNC(dateField, timestamp);

Specify the date field, such as year, month, day, etc., and a timestamp. Consequently, the timestamp will be rounded/truncated based on the specified date field.

Note: All the date field parts other than the targeted “date field” will be rounded to their initials. For instance, year, month, day, etc., will be rounded to “1”, and hours, minutes, seconds, etc., will be rounded to 0.

Example 1: How to Round/Truncate a Timestamp by Year in Postgres?

In the following snippet, we will specify a “year” as the first argument to truncate a timestamp by year in Postgres:

SELECT DATE_TRUNC('YEAR', DATE '2012-12-12');

The output snippet shows that the DATE_TRUNC() function rounded the given timestamp by nearest year.

Example 2: How to Round/Truncate a Current Timestamp by Day in Postgres?

Let’s learn how to round a timestamp by day in Postgres:

SELECT DATE_TRUNC('DAY', CURRENT_TIMESTAMP);

The output snippet shows that the DATE_TRUNC() function rounded/truncated the given timestamp by day.

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

Example 3: How to Group by YEAR in Postgres Via DATE_TRUNC() Function?

The following snippet shows the content of a sample table named "article_details":

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

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

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

This is how you round a timestamp in Postgres via the DATE_TRUNC() function.

Conclusion

In PostgreSQL, the DATE_TRUNC() function is a convenient way for truncating timestamps. The DATE_TRUNC() function enables you to specify the timestamp field as well as the unit of time to which you want to truncate. For instance, by specifying the 'year' as an argument, you can truncate a timestamp to the nearest year. This blog post explained the basic syntax, usage, and practical implementation of the DATE_TRUNC() function to round the timestamps in Postgres.