Get last 12 months data from Db with year in Postgres

Question

I want to fetch last 12 months data from db, I have written a query for that but that only giving me count and month but not year means month related to 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)

it gives me output like this

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

but I want year with month like 7 - 2012, or year in other col, doesn't matter

Include date_part('year', revision_timestamp) somewhere...? — deceze
– deceze ♦, Commented Sep 4, 2013 at 6:52

mvp · Accepted Answer · 2013-09-04 07:03:06Z

21

I believe 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

edited Sep 4, 2013 at 7:03

answered Sep 4, 2013 at 6:52

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

roman · Accepted Answer · 2013-09-04 06:58:57Z

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 by revision_timestamp > (current_date - INTERVAL '12 months'), you'll get range from current date in last year (so if today is '2013-09-04' you'll get range from '2012-09-04')

Collectives™ on Stack Overflow

Get last 12 months data from Db with year in Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related