Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to dynamically add interval to timestamp?

Tags:

oracle

plsql


I need at some point to increment dynamically a timestamp plsql variable.
So, instead of doing this:

timestamp_ := timestamp_ + INTERVAL '1' DAY;

I would like to do thomething like this:

timestamp_ := timestamp_ + INTERVAL days_ DAY;

It doesn't really work. My final goal is to dynamically create some scheduler jobs for some entities that have an variable expiration date, to avoid creating a single one which would be often executed.

like image 370
Michael Avatar asked Dec 12 '11 21:12

Michael


2 Answers

It sounds like you want

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

I would be somewhat cautious, however, about an architecture that involves creating thousands of scheduler jobs rather than one job that runs periodically to clear out expired rows. A single job is a heck of a lot easier to manage and oversee.

like image 137
Justin Cave Avatar answered Sep 19 '22 08:09

Justin Cave


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;
=============================
like image 32
Amol Gangadhar Naik Avatar answered Sep 21 '22 08:09

Amol Gangadhar Naik