Calculate exact month-difference between two dates

DB-Fiddle

CREATE TABLE inventory 
(
    id SERIAL PRIMARY KEY,
    inventory_date DATE,
    product_name VARCHAR(255),
    product_value VARCHAR(255)
);

INSERT INTO inventory (inventory_date, product_name, product_value)
VALUES ('2020-10-19', 'Product_A', '400'),
       ('2020-10-22', 'Product_B', '400'),
       ('2020-11-20', 'Product_C', '900'),
       ('2020-11-25', 'Product_D', '300');

Expected result:

    product_name | months_in_inventory
    -------------+--------------------
    Product_A    |       2
    Product_B    |       1
    Product_C    |       1
    Product_D    |       0

I want to calculate the months_in_inventory by calculating the difference between a fixed_date and the inventory_date.

In the example the fixed_date is '2020-12-20' and I am using it my query.

So far I am able to calculate the difference in days:

SELECT
    iv.product_name,
    '2020-12-20'::date - MAX(iv.inventory_date::date) AS days_in_inventory
FROM 
    inventory iv
GROUP BY 
    1
ORDER BY 
    1;

However, I could not figure out how to change it to a difference in month. Do you have any idea?

NOTE

I know that one way to approach this would be extracting the month from the fixed_date and inventory_date and subtract both numbers. However, this would not give me the correct result because I need it exactly based on the dates.

For example Product_B is only 1 month in inventory because 2020-10-22 is not two months compared to 2020-12-20.

Answer

You can use age(). If the value is always less than 12 months, then one method is:

SELECT iv.product_name,
       extract(month form age('2020-12-20'::date, MAX(iv.inventory_date::date))) AS months_in_inventory
FROM inventory iv
GROUP BY 1
ORDER BY 1;

A more accurate calculation takes the year into account:

SELECT iv.product_name,
       (extract(year from age('2020-12-20'::date, MAX(iv.inventory_date::date))) * 12 +
        extract(month from age('2020-12-20'::date, MAX(iv.inventory_date::date)))
       ) AS months_in_inventory
FROM inventory iv
GROUP BY 1
ORDER BY 1;

Here is a db<>fiddle.

Advertisement

Answer