PostgreSQL – DATE_TRUNC Function

In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision.

Syntax: date_trunc('datepart', field)

The datepart argument in the above syntax is used to truncate one of the field,below listed field type:

• millennium
• century
• decade
• year
• quarter
• month
• week
• day
• hour
• minute
• second
• milliseconds
• microseconds

The field argument holds either a TIMESTAMP type value or an INTERVAL type value to truncate. It can also be an expression that returns one of the above mentioned type. The date_trunc function generally returns a TIMESTAMP type value or an INTERVAL type value.

Example 1:

The following statement truncates a TIMESTAMP value to hour date part:

SELECT DATE_TRUNC('hour', TIMESTAMP '2020-03-17 02:09:30');

Output:

Example 2:

The below query counts the number of rentals by staff per year from the rental table of the sample database, ie, dvdrental:

SELECT
    staff_id,
    date_trunc('year', rental_date) y,
    COUNT (rental_id) rental
FROM
    rental
GROUP BY
    staff_id, y
ORDER BY
    staff_id

Output: