postgresql 在 Postgres 的间隔中使用可变周期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7796657/
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
Using a variable period in an interval in Postgres
提问by Belizzle
I have a relation that maintains monthly historical data. This data is added to the table on the last day of each month. A service I am writing can then be called specifying a month and a number of months prior for which to retrieve the historical data. I am doing this by creating startDate and endDate variables, and then returning data between the two. The problem I am having is that startDate is a variable number of months before endDate, and I cannot figure out how to use a variable period in an interval.
我有一个维护每月历史数据的关系。该数据在每个月的最后一天添加到表中。然后可以调用我正在编写的服务，指定要检索历史数据的月份和月份数。我通过创建 startDate 和 endDate 变量，然后在两者之间返回数据来做到这一点。我遇到的问题是 startDate 是 endDate 之前的可变月份数，我无法弄清楚如何在一个间隔中使用可变周期。
Here is what I have:
这是我所拥有的：
DECLARE
endDate TIMESTAMP := (DATE_TRUNC('MONTH',) + INTERVAL '1 MONTH') - INTERVAL '1 DAY';
startDate TIMESTAMP := endDate - INTERVAL 'MONTH';
I know that the line for startDate is not correct. How is this properly done?
我知道 startDate 的行不正确。这是如何正确完成的？
回答by A.H.
Use this line:
使用这一行：
startDate TIMESTAMP := endDate - ( || ' MONTH')::INTERVAL;
and note the space before MONTH.
Basically: You construct a string with like 4 MONTHand cast it with ::typeinto a proper interval.
并注意前面的空格MONTH。基本上：您使用 like 构造一个字符串4 MONTH并将其::type转换为适当的间隔。
Edit: I' have found another solution: You can calculate with intervallike this:
编辑：我找到了另一个解决方案：您可以这样计算interval：
startDate TIMESTAMP := endDate - * INTERVAL '1 MONTH';
This looks a little bit nicer to me.
这对我来说看起来更好一些。
回答by Mike Sherrill 'Cat Recall'
This code has nothing directly to do with your situation, but it does illustrate how to use variables in INTERVAL arithmetic. My table's name is "calendar".
此代码与您的情况没有直接关系，但它确实说明了如何在 INTERVAL 算术中使用变量。我的表的名称是“日历”。
CREATE OR REPLACE FUNCTION test_param(num_months integer)
RETURNS SETOF calendar AS
$BODY$
select * from calendar
where cal_date <= '2008-12-31 00:00:00'
and cal_date > date '2008-12-31' - ( || ' month')::interval;
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
