CODEWITHSUNDEEP
sqlpostgresql
Scott
Scott

Reputation: 77

Postgres Subtract Date Interval in Having

I am new to SQL but am having a (probably obvious) issue. My goal is to show all data in the month before the last dated entry. When executing the code block below to find the date a month ago,

SELECT MAX(created_at) - INTERVAL '1 MONTH' AS date
FROM shopify_view

it returns, '2019-11-27 11:40:06'. Makes sense!

But when I try to get all the date with a date above that value:

SELECT created_at AS date
FROM shopify_view
GROUP BY created_at
HAVING created_at >= MAX(created_at) - INTERVAL '1 MONTH'
ORDER BY created_at

it returns the first date as '2018-04-23 10:57:28'. Does not make sense!

what am I missing? Thank you!!

Upvotes: 0

Views: 50

Answers (1)

zip
zip

Reputation: 4061

You should use a subquery

SELECT *
FROM shopify_view
Where created_at >= (SELECT MAX(created_at) - INTERVAL '1 MONTH' AS date
FROM shopify_view)
ORDER BY created_at

Upvotes: 1

Related Questions