postgresql - SQL使用Date进行计算

Question

希望你们一切顺利!

我想学习如何使用 postgresql 比较日期(我使用 sakila 数据库)，我想回答以下问题？有人可以帮忙吗？我真的不知道如何处理日期部分。

/* 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

Answer 1

你可以使用:

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'