在Postgres中从Db获取过去12个月的数据和年份 [英] Get last 12 months data from Db with year in Postgres
本文介绍了在Postgres中从Db获取过去12个月的数据和年份的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从db中获取最近12个月的数据,我为此写了一个查询,但是只给我计数和月份而不提供年份意味着与哪一年相关的月份。
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)
它给了我这样的输出
month | count
-------+-------
7 | 21
8 | 4
9 | 10
但是我想要像7-2012这样的月份,或者其他col的年份,问题
but I want year with month like 7 - 2012, or year in other col, doesn't matter
推荐答案
我相信您想要这样:
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
这篇关于在Postgres中从Db获取过去12个月的数据和年份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文