Order timestamp durations using AGE function in PostgreSQL

Question

I have a bunch of DVD rental + return times that I'd like to sort by duration, from the shortest rental time (amount of time between timestamps) to the longest rental time. Is AGE the function I should use?

Here's my dysfunctional SQL so far:

  SELECT f.title, age(r.return_date, rental_date) FROM film f
JOIN inventory i
    ON f.film_id = i.film_id
JOIN rental r
    ON i.inventory_id = r.inventory_id
WHERE age(r.return_date, r.rental_date)
GROUP BY f.title
ORDER BY age ASC;

Be nice please, I'm a total SQL noob :)

Answer 1

age() is fine, but:

• I see no need for GROUP BY.
• I see no need for WHERE
• You need either an alias or to repeat the expression in the ORDER BY.

So:

SELECT f.title, age(r.return_date, r.rental_date)
FROM film f JOIN
     inventory i
     ON f.film_id = i.film_id JOIN
     rental r
     ON i.inventory_id = r.inventory_id
ORDER BY age(r.return_date, r.rental_date) ASC;