INTERVAL arithmetic

If you were running a real DVD Rental store, there would be times when you would need to determine what film titles were currently out for rental with customers. In the previous exercise, we saw that some of the records in the results had a NULL value for the return_date. This is because the rental was still outstanding.

Each rental in the film table has an associated rental_duration column which represents the number of days that a DVD can be rented by a customer before it is considered late. In this example, you will exclude films that have a NULL value for the return_date and also convert the rental_duration to an INTERVAL type. Here's a reminder of one method for performing this conversion.

SELECT INTERVAL '1' day * timestamp '2019-04-10 12:34:56'

This exercise is part of the course

Functions for Manipulating Data in PostgreSQL

View Course

Exercise instructions

Convert rental_duration by multiplying it with a 1 day INTERVAL
Subtract the rental_date from the return_date to calculate the number of days_rented.
Exclude rentals with a NULL value for return_date.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT
	f.title,
 	-- Convert the rental_duration to an interval
    ___ '1' ___ * ___.___,
 	-- Calculate the days rented as we did previously
    r.return_date - ___.___ AS days_rented
FROM film AS f
    INNER JOIN inventory AS i ON f.film_id = i.film_id
    INNER JOIN rental AS r ON i.inventory_id = r.inventory_id
-- Filter the query to exclude outstanding rentals
WHERE r.return_date ___ ___ ___
ORDER BY f.title;

Edit and Run Code