In PostgreSQL, the DATE_TRUNC() function trims unnecessary values from the date and time and returns a result with specific precision. In simple terms, DATE_TRUNC() extracts a TIMESTAMP/INTERVAL and truncates it to a specific level of precision.
This post will explain the usage of the DATE_TRUNC() function in Postgres through practical examples. So, let’s begin!
How to Use DATE_TRUNC() Function in Postgres?
The DATE_TRUNC() function takes a date part (like a year, month, etc.) and a TIMESTAMP as parameters. The DATE_TRUNC() function truncates the TIMESTAMP according to the specified date-part and returns the truncated part with a specific precision level.
Basic Syntax of DATE_TRUNC() Function
The below snippet explains the syntax of the DATE_TRUNC function:
DATE_TRUNC(date_part, Field);
● The Field argument represents an INTERVAL or TIMESTAMP value to be truncated.
● The date_part is the main argument of the DATE_TRUNC() function based on which the TIMESTAMP specified in the field argument will be truncated. The date_part accepts one of the following values:
- Microsecond, millisecond, second, minute, or hour.
- Decade, century, or millennium.
- Day, week, month, quarter, or year.
If you pass any of the values mentioned above to the DATE_TRUNC() function, then the TIMESTAMP will be rounded off to a whole value. For instance, if you pass month as an argument to the DATE_TRUNC() function, then all the values that came after the month will be reset to their initial values.
For example, seconds, minutes, etc., will be rounded to 00, while the months, years, etc., will be rounded to 01.
How Does the DATE_TRUNC() Function Work in PostgreSQL?
In this write-up, we will use the ‘2022-08-02 11:12:14’ TIMESTAMP in all the examples.
Example 1: How to Truncate a TIMESTAMP Value to Year?
We will pass the “Year” as a first argument to the DATE_TRUNC() function and a TIMESTAMP ‘2022-08-02 11:12:14’ as a second argument:
SELECT DATE_TRUNC('Year', TIMESTAMP '2022-08-02 11:12:14');
From the output, you can clearly observe that the Year’s value remains the same, i.e., 2022. However, the remaining values of the specified TIMESTAMP have been reset to their initial values.
Example 2: How to Truncate a TIMESTAMP Value to Month?
Specifying the month in the DATE_TRUNC() function will truncate all the instances of the TIMESTAMP that occurs after the Month:
SELECT DATE_TRUNC('Month', TIMESTAMP '2022-08-02 11:12:14');
On successful execution of the DATE_TRUNC function, the day, hours, minutes, and seconds have been reset to their initial values.
Example 3: How to Truncate a TIMESTAMP Value to Decade?
Let’s pass the DECADE as a date-part argument to the DATE_TRUNC() function and see how it works in PostgreSQL:
SELECT DATE_TRUNC('Decade', TIMESTAMP '2022-08-02 11:12:14');
From the output, you can clearly observe that everything that comes after the decade has been reset to their initial values.
Example 4: How to Truncate a TIMESTAMP Value to Day?
This example will let you know how to pass the day as a first parameter to the DATE_TRUNC() function:
SELECT DATE_TRUNC('Day', TIMESTAMP '2022-08-02 11:12:14');
The output clarifies that everything that comes after the day date-part has been reset to their initial values.
Example 5: How to Use the DATE_TRUNC() Function on Table’s Data?
Let’s run the below statement to fetch the bike_details table:
SELECT * FROM bike_details;
Let’s implement the DATE_TRUNC function on the bike_details table to truncate the year from the bike_launch_date column:
SELECT DATE_TRUNC('YEAR', bike_launch_date) FROM bike_details;
The output shows that the Year’s value remains the same. However, the remaining values, such as the month, day, etc., have been reset to their initial values.
Conclusion
The DATE_TRUNC() function in Postgres truncate a date or time value to a specific precision. It takes a date part (like a decade, year, month, etc.) and a TIMESTAMP as parameters, and then it truncates the TIMESTAMP according to the specified date part. Finally, it returns the truncated part with a specific precision level. This write-up explained how the date_trunc() function works in PostgreSQL with the help of multiple examples.
