postgresql date comparison: error: operator does not exist: date > integer

Question

Can anyone help me with formatting the following postgreql requests another way?

SELECT count(o.product_id), p.title FROM orders o, products p WHERE 
p.product_id = o.product_id AND o.order_date > current_date - 7 
GROUP BY p.title;

SELECT sum(p.price) AS total, o.order_date from products p, orders o 
WHERE p.product_id = o.product_id AND o.order_date > current_date - 7 
GROUP BY order_date;

I have tested the requests in Postico and PGweb with my tables and both commands give the desired output, but when i put them into my code i get the following error.

error: operator does not exist: date > integer

Here is a copy of the table schemas

orders(id PRIMARY KEY, order_number INTEGER, product_id INTEGER, user_id INTEGER,
tracking_id VARCHAR(50), order_date DATE)

products(product_id PRIMARY KEY, title VARCHAR(200), description VARCHAR(2500),
price NUMERIC(7,2), img TEXT, brand VARCHAR(50), horsepower INTEGER, 
deck_size INTEGER, product_type VARCHAR(5))

The product_id in the orders table references product_id in products.

Here are screenshots of the tables for visualization:

table definitions

SELECT count(o.product_id), p.title FROM orders o, products p WHERE p.product_id = o.product_id AND o.order_date > current_date - interval '7 days' GROUP BY p.title; use this, may help you out. — Bharti Mohane
– Bharti Mohane, Commented Sep 13, 2018 at 5:37
Both those queries you give above work just fine with those table definitions (I inserted int before PRIMARY KEY to get the table definitions to work) — Jasen
– Jasen, Commented Sep 13, 2018 at 6:05
o.order_date > current_date - 7 will work. Your query as shown works just fine, see here: rextester.com/VRO13048 I assume the error is somewhere else in your code. — user330315
– user330315, Commented Sep 13, 2018 at 6:31

Tim Biegeleisen · Accepted Answer · 2018-09-13 05:37:17Z

1

I am surprised that your query was running anywhere. If you want to subtract 7 days from a timestamp, you should be using:

current_date - INTERVAL '7 DAY'

So use this query:

SELECT
    p.title,
    COUNT(o.product_id)
FROM orders o
INNER JOIN products p
    ON p.product_id = o.product_id
WHERE 
    o.order_date > current_date - INTERVAL '7 DAY'
GROUP BY
    p.title;

Note: I replaced your implicit, old school join, with a modern explicit inner join. This is the preferred way of writing a join currently.

answered Sep 13, 2018 at 5:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

11 Comments

garrett yaworski Over a year ago

That worked. Thanks a lot for the tips. I've been coding for 5 weeks, so its a grind!!

garrett yaworski Over a year ago

I definitely will. it wont let me for a few minutes. thanks for the swift response.

Jasen Over a year ago

current_date - 7 is not an error, it results in a date but current_date - INTERVAL '7 DAY' gets you a timestamp

Tim Biegeleisen Over a year ago

@Jasen Feel free to edit my post if you want. Not sure what else I should be saying here.

Jasen Over a year ago

The problem is that the question has code that works perfectly (apart from the table definitions which are slightly incomplete). also the question is using date not timestamp.

|

Collectives™ on Stack Overflow

postgresql date comparison: error: operator does not exist: date > integer

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related