Add Int to DateTime

Question

I have a table with two columns RegisterDate and ActiveDays (int)

I want to add ActiveDays to RegisterDate and compare it to date of now

I usee below query

select RegisterDate  + INTERVAL  cast(ActiveDays as text) || ' day' .....

but it results in the error:

syntax error at or near "cast"

Answer 1

To explain the error you get, you have this:

INTERVAL  cast(ActiveDays as text) || ' day'

At first sight, this looks like it should work the same as:

INTERVAL '5 day'

But here INTERVAL isn't actually a cast operator, it is an annotation of the type of the literal string - basically, defining what the quote marks mean.

If you switch it over to a normal CAST, it would work:

CAST(CAST(ActiveDays as text) || ' day' as INTERVAL)

Or you can use Postgres's non-standard :: cast operator, which makes it a bit more readable:

(ActiveDays::text || ' day')::interval

Or, as klin's answer suggests, you could take a different approach, and multiply a single day by the appropriate integer:

ActiveDays * INTERVAL '1 day'

Note that here I am using the literal syntax that you tried to use earlier.