Calculate exact month-difference between two dates in redshift

Question

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.
In postgresSQL I was able to achieve the expected result with query from this question:

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;

However, when I run this query on redshift I get error:

ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

How do I need to change the query to make it work on redshift?

Gordon Linoff · Accepted Answer · 2021-04-09 12:41:48Z

0

Redshift doesn't support age(). Use months_between():

SELECT iv.product_name, FLOOR(MONTHS_BETWEEN('2020-12-20'::date, iv.inventory_date::date)) as months_in_inventory FROM inventory iv GROUP BY 1 ORDER BY 1;

answered Apr 9, 2021 at 12:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michi Over a year ago

What is the FLOOR function doing?

Gordon Linoff Over a year ago

@Michi . . . The return type is float. You want an integer: docs.aws.amazon.com/redshift/latest/dg/….

Bill Weiner · Accepted Answer · 2021-04-09 15:55:28Z

Gordon Linoff's answer is dead on. I just want to add a note for future readers of this post:

When you see "ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables." translate it to "ERROR: You used a leader-node only function with compute-node data. Use a different function."

This question comes up constantly here and in my consulting. I wish AWS would provide a more helpful error message and identify the leader-node only function in question. This has been the error message forever and the confusion around it continues.

Collectives™ on Stack Overflow

Calculate exact month-difference between two dates in redshift

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related