Question 178977 in PostgreSQL Question posted in
The official documentation can be found here.

How to calc diff btwn two timestamps in days in Postgresql?

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’

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    The simplest way is to use date arithmetic:

    WHERE localtimestamp - rental_date > '90 days'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search