Oracle date math manipulation Oracle Database Tips by Donald Burleson

You can use intrinsic Oracle functions to determine any date in the future:
 

Date / Time Math	Time Description
WHERE (date) > sysdate - 7/24;	Past 7 hours
WHERE (date) > sysdate - 7;	Past 7 days
WHERE (date) > sysdate - 7/1440;	Past 7 minutes
7/24 13/24	7 hours 13 hours
1/24/60/60 7/24/60/60	One second Seven seconds
1/24/60 5/24/60	One minute Five minutes
1/24 5/24	One hour Five hours
TRUNC(SYSDATE+1/24,'HH')	Every one hour starting with the next hour

For full examples of using Oracle data functions for scheduling, see Dr. Hall's book "Oracle Job Scheduling":

 

--  Schedule a snapshot to be run on this instance every hour

 

variable jobno number;

variable instno number;

begin

 

  select instance_number into :instno from v$instance;

-- ------------------------------------------------------------

-- Submit job to begin at 0600 and run every hour

-- ------------------------------------------------------------

dbms_job.submit(

   :jobno, 'statspack.snap;',

   trunc(sysdate)+6/24,

   'trunc(SYSDATE+1/24,''HH'')',

   TRUE,

   :instno);

 

-- ------------------------------------------------------------

-- Submit job to begin at 0900 and run 12 hours later

-- ------------------------------------------------------------

dbms_job.submit(

   :jobno,

   'statspack.snap;',

   trunc(sysdate+1)+9/24,

   'trunc(SYSDATE+12/24,''HH'')',

   TRUE,

   :instno);

 

-- ------------------------------------------------------------

-- Submit job to begin at 0600 and run every 10 minutes

-- ------------------------------------------------------------

dbms_job.submit(

   :jobno,

   'statspack.snap;',

   trunc(sysdate+1/144,'MI'),

   'trunc(sysdate+1/144,''MI'')',

   TRUE,

   :instno);

 

-- ----------------------------------------------------------

-- Submit job to begin at 0600 and run every hour, Monday - Friday

-- ---------------------------------------------------------

dbms_job.submit(
:jobno,
'statspack.snap;?,
trunc(sysdate+1)+6/24,
'trunc(
  least(
   next_day(SYSDATE - 1,'?MONDAY'?),
   next_day(SYSDATE - 1,''tUESDAY'?),
   next_day(SYSDATE - 1,'?WEDNESDAY'?),
   next_day(SYSDATE - 1,''tHURSDAY'?),
   next_day(SYSDATE - 1,'?FRIDAY'?)
)
+1/24,'?HH'?)',
TRUE,
:instno);

commit;
end;
/

---

Author Jeff Hunter also has examples of Oracle date math scheduling:

Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno,
   'statspack.snap;',
   trunc(sysdate,'HH24')+
     ((floor(to_number(to_char(sysdate,'MI'))/5)+1)*5)/(24*60),
   'trunc(sysdate,''HH24'')+
     ((floor(to_number(to_char(sysdate,''MI''))/5)+1)*5)/(24*60)',
   TRUE, :instno);
COMMIT;
END;
/


--------------------------------------------------------------------------------

Run Statspack Snapshot Every 15 Minutes Starting at the Next 15 Minute Interval

variable jobno number;
variable instno number;

BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/15)+1)*15)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/15)+1)*15)/(24*60)', TRUE, :instno);
COMMIT;
END;
/

--------------------------------------------------------------------------------

Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, :instno);
COMMIT;
END;
/


--------------------------------------------------------------------------------

Run Statspack Snapshot Every 1 Hour

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);
COMMIT;
END;
/

--------------------------------------------------------------------------------

DBMS_JOB / Every 15 Minutes from Monday to Friday, Between 6 a.m. and 6 p.m.

SQL> ALTER SESSION SET nls_date_format = '(DY) MON DD, YYYY HH24:MI';
Session altered.

SQL> SELECT
sysdate
, CASE
WHEN ( TO_CHAR(SYSDATE, 'HH24') BETWEEN 6 AND 17
AND
TO_CHAR(SYSDATE, 'DY') NOT IN ('SAT','SUN')
)
THEN TRUNC(sysdate) +
(TRUNC(TO_CHAR(sysdate,'sssss')/900)+1)*15/24/60
WHEN (TO_CHAR(sysdate, 'DY') NOT IN ('FRI','SAT','SUN'))
THEN TRUNC(sysdate)+1+6/24
ELSE next_day(trunc(sysdate), 'Mon') + 6/24
END interval_date
FROM dual;

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.