Using DATE_TRUNC
The DATE_TRUNC() function will truncate timestamp or interval data types to return a timestamp or interval at a specified precision. The precision values are a subset of the field identifiers that can be used with the EXTRACT() and DATE_PART() functions. DATE_TRUNC() will return an interval or timestamp rather than a number. For example
SELECT DATE_TRUNC('month', TIMESTAMP '2005-05-21 15:30:30');
Result: 2005-05-01 00;00:00
Now, let's experiment with different precisions and ultimately modify the queries from the previous exercises to aggregate rental activity.
This exercise is part of the course
Functions for Manipulating Data in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Truncate rental_date by year
SELECT ___(___, ___) AS rental_year
FROM rental;