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:
Last Updated :
01 Feb, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...