CODEWITHSUNDEEP
postgresqldatetime
nycdan
nycdan

Reputation: 2839

Find new records for each of the past n months in PostgreSQL

I have a table of records with a createddate in them. I would like to return the new records for each of the last 6 calendar months, including the partial this month. I know SQL Server well but am not as familiar with PostgreSQL.

I have been able to get data for rolling months with this query:

select 
COUNT(ID) as Total,
COUNT(CASE WHEN createddate between (now() - '1 month'::interval)::timestamp AND now() THEN AG.ID END) as ThisMonth,
COUNT(CASE WHEN createddate between (now() - '2 month'::interval)::timestamp AND (now() - '1 month'::interval)::timestamp THEN AG.ID END) as LastMonth,
COUNT(CASE WHEN createddate between (now() - '3 month'::interval)::timestamp AND (now() - '2 month'::interval)::timestamp THEN AG.ID END) as PrevMonth,
COUNT(CASE WHEN createddate between (now() - '4 month'::interval)::timestamp AND (now() - '3 month'::interval)::timestamp THEN AG.ID END) as PrevMonth2,
COUNT(CASE WHEN createddate between (now() - '5 month'::interval)::timestamp AND (now() - '4 month'::interval)::timestamp THEN AG.ID END) as PrevMonth3,
COUNT(CASE WHEN createddate between (now() - '6 month'::interval)::timestamp AND (now() - '5 month'::interval)::timestamp THEN AG.ID END) as PrevMonth4
FROM a_group AG

But on 6/21, this will return data from 5/22-6/21, 4/22-5/21, etc.
I would like the data to bucket as follows: 6/1-6/21 (partial current month), 5/1-5/31, etc.

Any suggestions? I also suspect I could do this in a loop but am not familiar enough with the syntax yet. For now, I am testing this from PostgreSQL Maestro against a backup file.

Thanks.

Upvotes: 1

Views: 2388

Answers (1)

Tom
Tom

Reputation: 4782

I think the date_trunc function may be your friend (see postgres docs). You would do something like this I guess:

select 
COUNT(ID) as Total,
COUNT(CASE WHEN createddate between date_trunc('month', now()) AND now() THEN AG.ID END) as ThisMonth,
COUNT(CASE WHEN createddate between date_trunc('month', now()) - interval '1 month' AND date_trunc('month', now()) - interval '1 day' THEN AG.ID END) as LastMonth,

etc...

Upvotes: 5

Related Questions