Postgres INTERVAL using value from table

Question

If I want to add 5 days to a date, I can do it using the INTERVAL function:

select create_ts + interval '5 days' from abc_company;

However, my table has a field called num_of_days and I want to add it to my create_ts. Something like this:

select create_ts + interval num_of_days || ' days' from abc_company;

This does not work. How can I accomplish this in postgresql?

Answer 1

Simply multiply the value with an interval:

select create_ts + num_of_day * interval '1' day 
from abc_company;

Since Postgres 9.4 this is easier done using the make_interval() function:

select create_ts + make_interval(days => num_of_day)
from abc_company;

Answer 2

You just need a working type cast. This kind is standard SQL.

select current_timestamp + cast((num_of_days || ' days') as interval)
from abc_company;

This is an alternative syntax, peculiar to PostgreSQL.

select current_timestamp + (num_of_days || ' days')::interval
from abc_company;

I prefer not trying to remember the third kind of type cast supported by PostgreSQL, which is the function-like syntax.

select current_timestamp + "interval" (num_of_days || ' days')
from abc_company;

Why? Because some function names have to be quoted; interval is one of them.

Also, the names interval, time, and timestamp can only be used in this fashion if they are double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided.

Answer 3

here is a function that I use:

CREATE OR REPLACE FUNCTION DateAdd(diffType varchar(15), incrementValue int, inputDate timestamp) RETURNS timestamp AS $$
DECLARE
   YEAR_CONST Char(15) := 'year';
   MONTH_CONST Char(15) := 'month';
   WEEK_CONST Char(15) := 'week';
   DAY_CONST Char(15) := 'day';
   HOUR_CONST Char(15) := 'hour';

   dateTemp timestamp;
   intervals interval;
BEGIN
    IF lower($1) = lower(YEAR_CONST) THEN
        select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals;
    ELSEIF lower($1) = lower(MONTH_CONST) THEN
        select cast(cast(incrementvalue as character varying) || ' months' as interval) into intervals;
    ELSEIF lower($1) = lower(DAY_CONST) THEN
        select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals;
    ELSEIF lower($1) = lower(WEEK_CONST) THEN
        select cast(cast(incrementvalue as character varying) || ' week' as interval) into intervals;
    ELSEIF lower($1) = lower(HOUR_CONST) THEN
        select cast(cast(incrementvalue as character varying) || ' hour' as interval) into intervals;
    END IF;

   dateTemp := inputdate + intervals;

   RETURN dateTemp;
END;
$$ LANGUAGE plpgsql;

Used like so:

select dateadd('day', 3, current_timestamp);

It supports adding years, months, weeks, days, hours. More support could be added