How to dynamically add an interval to a timestamp?

Question

I need at some point dynamically increment the plsql timestamp variable.

So instead:

timestamp_ := timestamp_ + INTERVAL '1' DAY; 

I would like to do something like this:

 timestamp_ := timestamp_ + INTERVAL days_ DAY; 

This does not work. My ultimate goal is to dynamically create some scheduler jobs for some objects that have a variable expiration date in order to avoid creating one that will often run.

Answer 1

Looks like you want

 timestamp_ := timestamp + numtodsinterval( days_, 'day' ); 

I would be somewhat cautious about the architecture, which includes the creation of thousands of tasks for the scheduler, rather than one task that runs periodically to clear the expired lines. The only task is much easier to manage and control.

Answer 2

 Special note: 1. INTERVAL YEAR TO MONTH and 2. INTERVAL DAY TO SECOND are the only two valid interval datatypes; Sample Example: ============================= DECLARE l_time INTERVAL YEAR TO MONTH; l_newtime TIMESTAMP; l_year PLS_INTEGER := 5; l_month PLS_INTEGER := 11; BEGIN -- Notes : -- 1. format is using "-" to connect year and month -- 2. No need to mention any other keyword ; Implicit conversion takes place to set interval l_time := l_year || '-' || l_month; DBMS_OUTPUT.put_line ( l_time ); SELECT SYSTIMESTAMP + l_time INTO l_newtime FROM DUAL; DBMS_OUTPUT.put_line ( 'System Timestamp :' || SYSTIMESTAMP ); DBMS_OUTPUT.put_line ( 'New Timestamp After Addition :' || l_newtime ); END; ============================= 

Answer 3

Try the following:

  DECLARE l_val NUMBER; l_result VARCHAR2( 20 ); BEGIN l_val := 1; SELECT SYSDATE - INTERVAL '1' DAY * l_val INTO l_result FROM DUAL; DBMS_OUTPUT.put_line( 'Current Date is ' || SYSDATE || ' minus ' || l_val || ' day(s) is ' || l_result ); END; 

The output will be:
Current date - 25-FEB-16 minus 1 day - 24-FEB-16