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屋!