Get past 12 months of data from Db with a year in Postgres

Question

I want to get the latest data for 12 months from db, I wrote a query for this, but only the calculation and the month, but not the year, mean the month associated with which year.

My sql:

Select count(B.id),date_part('month',revision_timestamp) from package AS A INNER JOIN package_revision AS B ON A.revision_id=B.revision_id WHERE revision_timestamp > (current_date - INTERVAL '12 months') GROUP BY date_part('month',revision_timestamp) 

he gives me a conclusion like this

  month | count -------+------- 7 | 21 8 | 4 9 | 10 

but I want a year with a month, like 7 - 2012, or a year in another column, it does not matter.

Answer 1

I believe that you wanted this:

 SELECT to_char(revision_timestamp, 'YYYY-MM'), count(b.id) FROM package a JOIN package_revision b ON a.revision_id = b.revision_id WHERE revision_timestamp > date_trunc('month', CURRENT_DATE) - INTERVAL '1 year' GROUP BY 1 

Answer 2

 select count(B.id), date_part('year', revision_timestamp) as year, date_part('month',revision_timestamp) as month from package as A inner join package_revision as B on A.revision_id=B.revision_id where revision_timestamp > (current_date - INTERVAL '12 months') group by date_part('year', revision_timestamp) date_part('month', revision_timestamp) 

or

 select count(B.id), to_char(revision_timestamp, 'YYYY-MM') as month from package as A inner join package_revision as B on A.revision_id=B.revision_id where revision_timestamp > (current_date - INTERVAL '12 months') group by to_char(revision_timestamp, 'YYYY-MM') 

Keep in mind that if you filter revision_timestamp > (current_date - INTERVAL '12 months') , you will get a range from the current date last year (so if today is '2013-09-04' , you will get a range from '2012-09-04' )