Exercise

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.

Instructions 1/4

undefined XP
  • 1

    Truncate the rental_date field by year.

  • 2

    Now modify the previous query to truncate the rental_date by month.

  • 3

    Let's see what happens when we truncate by day of the month.

  • 4

    Finally, count the total number of rentals by rental_day and alias it as rentals.