datetime - PostgreSQL how to concat interval value '2 days'

Question

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?

With reference to the following page http://www.postgresql.org/docs/8.0/static/functions-datetime.html

Answer 1

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

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

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

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;

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