PostgreSQL如何连续间隔值'2天' [英] PostgreSQL how to concat interval value '2 days'

问题描述

在PostgreSQL中，我想连接 current_timestamp 与间隔如下：

In PostgreSQL I want to concat the current_timestamp with an interval as follows:

select current_timestamp + interval 2||' days'

但是当我这样做时，我会收到一个错误：

But when I do, I get an error:

[Err] ERROR:  syntax error at or near "2"
LINE 1: select current_timestamp + interval 2||' days'

但是如果我这样做，它可以正常工作：

But if I do it like this, it works correctly:

select current_timestamp + interval '2 days'

为什么一个工作，但不是另一个？

Why does one work, but not the other?

参考以下页面
http://www.postgresql.org/docs/8.0/static /functions-datetime.html

推荐答案

部分问题是间隔时间的标准SQL表达式引用数字，但不是关键字。所以你必须要小心。

Part of the problem is that the standard SQL expression for intervals quotes the number, but not the keywords. So you have to be careful.

select current_date, current_date + interval '2' day;
--
2012-02-21   2012-02-23 00:00:00

在PostgreSQL中，引用像2天和2天也可以。所以你可能会认为'2'|| 'days'将是等同的，但不是。

In PostgreSQL, quoting like '2 day' and '2 days' also works. So you might think that '2' || ' days' would be equivalent, but it's not.

select current_date, current_date + interval '2' || ' days';
--
2012-02-21   2012-02-21 00:00:02 days

如AH所述，解决方案是将结果字符串转换为间隔。

The solution, as A.H. said, is to cast the result string as an interval.

您还可以使用一个变量代替2.这将生成2012年的日历。

You can also use a variable in place of 2. This generates a calendar for 2012.

-- 0 to 365 is 366 days; 2012 is a leap year.
select ('2012-01-01'::date + (n || ' days')::interval)::date calendar_date
from generate_series(0, 365) n;

我使用最终的转换日期，因为date + interval返回时间戳。

I use that final cast to date, because date + interval returns a timestamp.

这篇关于PostgreSQL如何连续间隔值'2天'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！