CODEWITHSUNDEEP
postgresql
fyardlest
fyardlest

Reputation: 298

SQL using Date to perfrom calculation

hope you doing well guys!

I would like the learn how to compare dates using postgresql (I use the sakila database), and I would like to answer the following question? Can someone help please? I really don't know what to do with the date part.

/* Which customers rented movies for 5 days or more? */
SELECT 
    DISTINCT 
    c.first_name || ' ' || c.last_name AS customer_full_name
FROM 
    customer c, rental r
WHERE 
    c.customer_id = r.customer_id 
    and date('2005-01-01 22:54:33') - date('2006-12-31 22:54:33') >= 5

Upvotes: 0

Views: 28

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You could use:

SELECT  DISTINCT 
    c.first_name || ' ' || c.last_name AS customer_full_name
FROM customer c
JOIN rental r
  ON c.customer_id = r.customer_id 
WHERE date_of_return::date - date_of_rental::date >= 5;
-- alternatively
-- WHERE date_of_return - date_of_rental >= interval '5 days'

Upvotes: 2

Related Questions