CODEWITHSUNDEEP
postgresql
DJ_Stuffy_K
DJ_Stuffy_K

Reputation: 635

how to retrieve results for past n days using postgresql

I'm new to postgres

What I'm trying to do: Get results across previous n days everytime I run the query on a given day without manually updating the start and end dates.

example query:
If I were running the query today (June 23rd,2019)
My query to retrieve results for the past 8 days would look like

select count(*),state,DATE(date)
from animal_shelter
where date=>'2019-06-15' and date <='2019-06-22'
group by state,DATE(date);

but this requires manual intervention where I'm updating two dates every single time.

I found that there's 

now()::date - interval '8 days'

which can be used but I didn't understand how to modify use it with the between condition.

Possible solution

select count(*),state,DATE(date)
from animal_shelter
where date=> ( now()::date - interval '8 days') 
group by state,DATE(date);

Is this right, and can I be assured that it picks exactly the 8 past days worth of data not including today?

Please help me accomplish my goal.

Upvotes: 1

Views: 1406

Answers (2)

kRazzy R
kRazzy R

Reputation: 1589

you can also make use of the current_date function mentioned here: https://www.postgresql.org/docs/current/functions-datetime.html

so the answer would be like 

where date = current_date - interval 'n day'

Upvotes: 0

Jeremy
Jeremy

Reputation: 6723

I believe you are looking for:

WHERE date between now()::date - interval '8 days' AND now()::date - interval '1 day';

Upvotes: 3

Related Questions