问题描述
我有一个维护每月历史数据的关系.该数据在每个月的最后一天添加到表中.然后可以调用我正在编写的服务,指定要检索历史数据的月份和月份数.我通过创建 startDate 和 endDate 变量,然后在两者之间返回数据来做到这一点.我遇到的问题是 startDate 是 endDate 之前的可变月份数,我无法弄清楚如何在一个间隔中使用可变周期.
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.
这是我所拥有的:
DECLARE
endDate TIMESTAMP := (DATE_TRUNC('MONTH',$2) + INTERVAL '1 MONTH') - INTERVAL '1 DAY';
startDate TIMESTAMP := endDate - INTERVAL $3 'MONTH';
我知道 startDate 的行不正确.这是如何正确完成的?
I know that the line for startDate is not correct. How is this properly done?
推荐答案
使用这一行:
startDate TIMESTAMP := endDate - ($3 || ' MONTH')::INTERVAL;
并注意MONTH
之前的空格.基本上:您构造一个类似 4 MONTH
的字符串,并用 ::type
将其转换为适当的间隔.
and note the space before MONTH
.Basically: You construct a string with like 4 MONTH
and cast it with ::type
into a proper interval.
编辑:我找到了另一种解决方案:您可以像这样使用 interval
进行计算:
Edit: I' have found another solution: You can calculate with interval
like this:
startDate TIMESTAMP := endDate - $3 * INTERVAL '1 MONTH';
这对我来说看起来更好一些.
This looks a little bit nicer to me.
这篇关于在 Postgres 的间隔中使用可变周期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!