How to use date_trunc function in PostgreSQL

Are you having a complex timestamp in your Postgres database? Don’t worry, we have a solution for you in this post. The date_trunc function of Postgres enables you to round off the date-field/timestamp to a precision value. The date_trunc function refers to the Postgres database but apart from targeting a date field of a database, the date_trunc function directly accepts a timestamp value. These dimensions of the date_trunc function are referred to as inside and outside the functionality of the date_trunc function. This brief guide describes the functionality of the date_trunc function on Postgres databases.

Prerequisites

The following set of prerequisites are recommended to proceed for a better understanding:

• An actively running Postgres service is required.
• A Postgres database and several columns (with date fields) must be present to apply the date_trunc function.

How Postgres date_trunc works

To apply the date_trunc function, the syntax provided below would assist:

Majorly the syntax accepts two fields, and these are defined below:

date-part: The date_trunc function depends on this parameter as the precision of the timestamp rests on this. The date-part may contain one of the following values:

All these values round off the timestamp/date-field into a whole value. If the year is passed through the date_trunc function, then all the timestamp values that are followed by the year will be rounded off to their initial values. For instance, the value of month and day will be 01(as month and day start from 01). However, the hour, minute, and second values will be 00.

field: This value of syntax accepts either the complete timestamp or the field(column) that has a date(timestamp) stored in it.

How to use the date_trunc function

After going through the syntax portion, let’s practice the date_trunc function with several properties.

We have selected the timestamp ‘2021-12-07 04:02:30‘ that contains a year followed by month, day, hour, minute, and seconds. In this example, the timestamp is passed to the date_trunc function directly.

Using ‘year’ as a date-part argument: Following the instances of this timestamp, the below-mentioned date_trunc function presents the timestamp by considering year as a date-part argument.

Using ‘month’ as a date-part argument: The ‘month’ can also be used as the date-part argument. To do so, you have to pass it in a date_trunc function as shown below. The date_trunc function will round off all the instances that occur after a month (like day, hour, minute, second ).

Using ‘day’ as a date-part argument: This part describes the output by using the day as a date-part argument of the date_trunc function. To do so, the command is provided below:

Using ‘hour’ as a date-part argument: This part provides the usage of ‘hour’ as a date part in the date_trunc function of Postgres. The command written below makes use of the date_trunc function to round off the TIMESTAMP. It is observed that the entries followed by an hour (minutes and seconds) are rounded off to their initial values (00):

The use of the date_trunc function can be extended for other entries like minutes and seconds as well.

Conclusion

The date_trunc function of Postgres allows the Postgres user to round off the date field to any timestamp instance. The time stance may vary from the second, minute, hour, and so on. This article