How to calculate difference between two timestamps in days

Question

CREATE TABLE IF NOT EXISTS detailed(
customer_id smallint NOT NULL,
store_id smallint NOT NULL,
first_name varchar(45),
last_name varchar(45),
email varchar (50),
rental_id INTEGER NOT NULL,
rental_date timestamp without time ZONE,
inventory_id INTEGER NOT NULL
);

INSERT INTO detailed(
customer_id,store_id,first_name,last_name,email,rental_id,rental_date,inventory_id) 
SELECT 
customer.customer_id,
customer.first_name,
customer.last_name,
customer.email,
rental.rental_id,
rental.rental_date,
rental.inventory_id
FROM customer
INNER JOIN rental ON customer.customer_id=rental.customer_id
WHERE CURRENT_DATE - rental.rental_date > 90 AND rental.return_date = NULL;

I'm attempting to populate a table with information on customer rentals that have rentals exceeding 90 days. To do so, I am subtracting rental_date from current_date and returning results that are > 90 days. What is the best way to achieve this?

I attempted to use the DATEDIFF function, but did not recognize the column 'day'

Answer 1

You might want to use the age function that postgres offers as part of the Date/Time Functions and Operators. Then use extract so you can just compare the days component of the interval.

Your where predicate could look like

where extract(day from age(now(), rental.rental_date)) > 90 and rental.return = NULL;

Answer 2

The simplest way is to use date arithmetic:

WHERE localtimestamp - rental_date > '90 days'