Open In App

PostgreSQL – DATE_TRUNC Function

Improve
Improve
Like Article
Like
Save
Share
Report

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:


Last Updated : 01 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads