Job Intervals and Date Arithmetic

There are times when a DBA has to schedule jobs from inside the database either via dbms_jobs or dbms_scheduler to automate the tasks. Most of the times the confusion arrives on how to schedule the tasks with date and time. The below can be used in the interval parameter to schedule a job on its desired time.

Every Second.

interval => 'SYSDATE +1/86400';

Every Minute.

interval => 'SYSDATE +1/1440';

Every Ten Minute.
interval => 'SYSDATE +10/1440';

Every Half Hour.
interval => 'SYSDATE +30/1440';

Every Hour.
interval => 'SYSDATE +60/1440';

Every Day.
interval => 'SYSDATE +1';

Every Day with Fixed Time. ex: every night 8:00 pm.
interval => 'trunc(SYSDATE +1) + 20/24'

Every Midnight 12:00.
interval => 'trunc(SYSDATE +1)'

Every Week
interval => 'trunc(SYSDATE +7)'

Every Month
interval => 'add_months(SYSDATE, 1)'

Happy scheduling!