postgresql generate_series 几个月 [英] Postgresql generate_series of months
问题描述
我正在尝试使用 generate_series 函数在 PostgreSQL 中生成一个系列.我需要从 2008 年 1 月到 current month + 12
(一年后)的一系列月份.我正在使用并仅限于 PostgreSQL 8.3.14(所以我在 8.4 中没有时间戳系列选项).
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).
我知道如何获得一系列的日子,例如:
I know how to get a series of days like:
select generate_series(0,365) + date '2008-01-01'
但我不知道该怎么做.
推荐答案
select DATE '2008-01-01' + (interval '1' month * generate_series(0,11))
编辑
如果您需要动态计算数字,以下方法可能会有所帮助:
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
这会计算自 2008-01-01 以来的月数,然后在其上加上 12.
This calculates the number of months since 2008-01-01 and then adds 12 on top of it.
但我同意 Scott 的观点:你应该把它放到一个集合返回函数中,这样你就可以做类似 select * from calc_months(DATE '2008-01-01')
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')
这篇关于postgresql generate_series 几个月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!