Postgresql generate_series 月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7450515/
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
Postgresql generate_series of months
提问by Pyrite
I'm trying to generate a series in PostgreSQL with the generate_series function. I need a series of months starting from Jan 2008 until current month + 12(a year out). I'm using and restricted to PostgreSQL 8.3.14 (so I don't have the timestamp series options in 8.4).
我正在尝试使用 generate_series 函数在 PostgreSQL 中生成一个系列。我需要从 2008 年 1 月到current month + 12（一年后）的一系列月份。我正在使用并且仅限于 PostgreSQL 8.3.14（所以我在 8.4 中没有时间戳系列选项）。
I know how to get a series of days like:
我知道如何获得一系列的日子，例如：
select generate_series(0,365) + date '2008-01-01'
But I am not sure how to do months.
但是我不知道怎么办了。
回答by a_horse_with_no_name
select DATE '2008-01-01' + (interval '1' month * generate_series(0,11))
Edit
编辑
If you need to calculate the number dynamically, the following could help:
如果您需要动态计算数字，以下方法可能会有所帮助：
select DATE '2008-01-01' + (interval '1' month * generate_series(0,month_count::int))
from (
select extract(year from diff) * 12 + extract(month from diff) + 12 as month_count
from (
select age(current_timestamp, TIMESTAMP '2008-01-01 00:00:00') as diff
) td
) t
This calculates the number of months since 2008-01-01 and then adds 12 on top of it.
这将计算自 2008-01-01 以来的月数，然后在其上加上 12。
But I agree with Scott: you should put this into a set returning function, so that you can do something like select * from calc_months(DATE '2008-01-01')
但我同意 Scott：你应该把它放到一个集合返回函数中，这样你就可以做类似的事情 select * from calc_months(DATE '2008-01-01')
回答by patrick
You can interval generate_series like this:
您可以像这样间隔 generate_series：
SELECT date '2014-02-01' + interval '1' month * s.a AS date
FROM generate_series(0,3,1) AS s(a);
Which would result in:
这将导致：
date
---------------------
2014-02-01 00:00:00
2014-03-01 00:00:00
2014-04-01 00:00:00
2014-05-01 00:00:00
(4 rows)
You can also join in other tables this way:
您还可以通过这种方式加入其他表：
SELECT date '2014-02-01' + interval '1' month * s.a AS date, t.date, t.id
FROM generate_series(0,3,1) AS s(a)
LEFT JOIN <other table> t ON t.date=date '2014-02-01' + interval '1' month * s.a;
回答by RousseauAlexandre
You can interval generate_serieslike this:
你可以generate_series像这样间隔：
SELECT TO_CHAR(months, 'YYYY-MM') AS "dateMonth"
FROM generate_series(
'2008-01-01' :: DATE,
'2008-06-01' :: DATE ,
'1 month'
) AS months
Which would result in:
这将导致：
dateMonth
-----------
2008-01
2008-02
2008-03
2008-04
2008-05
2008-06
(6 rows)
回答by RousseauAlexandre
Well, if you only need months, you coulddo:
好吧，如果你只需要几个月，你可以这样做：
select extract(month from days)
from(
select generate_series(0,365) + date'2008-01-01' as days
)dates
group by 1
order by 1;
and just parse that into a date string...
并将其解析为日期字符串...
But since you know you'll end up with months 1,2,..,12, why not just go with select generate_series(1,12);?
但是既然你知道你最终会得到 1,2,..,12 个月，为什么不直接使用select generate_series(1,12);？
