postgresql 使用 Postgres 中的年份从 Db 中获取过去 12 个月的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18607110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get last 12 months data from Db with year in Postgres
提问by Inforian
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.
我想从数据库中获取过去 12 个月的数据，我已经为此编写了一个查询，但只给我计数和月份而不是年份意味着与哪一年相关的月份。
My Sql :
我的 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
但我想要像 7 - 2012 这样的月份，或者其他 col 中的年份，没关系
回答by mvp
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
回答by Roman Pekar
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')
请记住，如果您按 过滤revision_timestamp > (current_date - INTERVAL '12 months')，您将获得去年当前日期的范围（因此，如果今天是，'2013-09-04'您将获得范围自'2012-09-04'）
