postgresql PostgreSQL如何连接间隔值'2天'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9376350/
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
PostgreSQL how to concat interval value '2 days'
提问by d-man
In PostgreSQL I want to concat the current_timestampwith an intervalas follows:
在 PostgreSQL 中，我想current_timestamp用 an 连接interval，如下所示：
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?
为什么一个有效，而另一个无效？
With reference to the following page http://www.postgresql.org/docs/8.0/static/functions-datetime.html
参考以下页面 http://www.postgresql.org/docs/8.0/static/functions-datetime.html
回答by Mike Sherrill 'Cat Recall'
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.
部分问题在于区间的标准 SQL 表达式引用了数字，而不是关键字。所以你必须小心。
select current_date, current_date + interval '2' day;
--
2012-02-21 2012-02-23 00:00:00
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.
在 PostgreSQL 中，像 '2 day' 和 '2 days' 这样的引用也有效。所以你可能认为 '2' || 'days' 是等价的，但事实并非如此。
select current_date, current_date + interval '2' || ' days';
--
2012-02-21 2012-02-21 00:00:02 days
The solution, as A.H. said, is to cast the result string as an interval.
正如AH所说，解决方案是将结果字符串转换为间隔。
You can also use a variable in place of 2. This generates a calendar for 2012.
您还可以使用变量代替 2。这将生成 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;
I use that final cast to date, because date + interval returns a timestamp.
到目前为止，我使用了最后一次转换，因为日期 + 间隔返回一个时间戳。
回答by A.H.
Please try this syntax:
请尝试以下语法：
select current_timestamp + ( 2 || ' days')::interval;
or even this one:
甚至这个：
select current_timestamp + 2 * interval '1 day';
