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'
Este exercício faz parte do curso
Functions for Manipulating Data in PostgreSQL
Instruções do exercício
- Convert
rental_durationby multiplying it with a 1 dayINTERVAL - Subtract the
rental_datefrom thereturn_dateto calculate the number ofdays_rented. - Exclude rentals with a
NULLvalue forreturn_date.
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
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;