在Postgres中的间隔中使用可变周期 [英] Using a variable period in an interval in Postgres

问题描述

我有一个维护每月历史数据的关系。该数据在每个月的最后一天添加到表中。然后，可以调用我正在编写的服务，指定要检索历史数据的一个月和几个月。我通过创建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中的间隔中使用可变周期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！