在 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屋！