How to get data from last x months Postgres Sql Query where date field is a timestamp?

6
votes

I'm trying to write a sql query where I can get data from the last 3 months. Specifically, I want to see how much each customer spent over the last 3 months. My date field is a timestamp with timezone (e.g., 2017-07-14 00:56:43.833191+00").

I'm not trying to get the last 90 days of data, but actually the last 3 months, so if it's currently July 14, 2017, I'd want to get data between April 1, 2017 and June 30, 2017.

Here's what I currently have and it works great if the 3 months are in the same year, but it doesn't work across years, meaning that if the current date is February 15, 2017, I'd want it to return data from November 1, 2016 through January 31, 2017. But, that doesn't work.

Here's my current query. I'd appreciate any help. Thanks!

select sum(amount), customer_id
from payments
where (date_part('month',payment_date) < (date_part('month',current_timestamp)-1) and
       date_part('month',payment_date) >=  (date_part('month',current_timestamp)-3) ) and 
       (date_part('year',date_created) = date_part('year',current_timestamp))
group by customer_id
1
sqlpostgresql

1 Answers

10
votes

Hmmm . . . I think date_trunc() is much simpler:

select sum(amount), customer_id
from payments
where payment_date >= date_trunc('month', now()) - interval '3 month' and
      payment_date < date_trunc('month', now())
group by customer_id;