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?
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;
interval '1 day'. I wonder why the string is needed at all in the expression.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.
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
