DBA Tips Archive for Oracle

  

Expressing Time with Fractions - (Date Arithmetic)

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. Job Intervals and Date Arithmetic
  3. Job Intervals Examples
  4. Oracle Date Math Examples
  5. Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval
  6. Run Statspack Snapshot Every 15 Minutes Starting at the Next 15 Minute Interval
  7. Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval
  8. Run Statspack Snapshot Every 1 Hour
  9. DBMS_JOB / Every 15 Minutes from Monday to Friday, Between 6 a.m. and 6 p.m.
  10. Execute a Job Every xy Hours Starting at an Arbitrary Start Time


Overview

The following article provides a cheat sheet for handling date/time math in Oracle. One of the core issues to keep in mind is that Oracle date math is based on a day.


Job Intervals and Date Arithmetic

Job execution intervals are determined by the date expression set by the interval parameter. One key to setting the interval correctly is determine which of the following applies to the job:

  1. Each execution of the job should follow the last by a specific time interval.
  2. The job should execute on specific dates and times.

Type 1 Jobs

Jobs of type 1 usually have relatively simple date arithmetic expressions of the type SYSDATE+N, where N represents the time interval expressed in days. The following table provides examples of these types of intervals.

Action Interval Time
Execute daily 'SYSDATE + 1'
Execute every 4 hours 'SYSDATE + 4/24'
Execute every 10 minutes 'SYSDATE + 10/1440'
Execute every 30 seconds 'SYSDATE + 30/86400'
Execute every 7 days 'SYSDATE + 7'
Do no re-execute and remove job NULL

NOTE: Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with in interval of 'SYSTEM + 1', it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. the next day using DBMS_JOB.RUN, then it will be rescheduled for execution at 4:00 p.m. the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case, the job will run as soon as it can, but the execution time will have migrated away from the original submission time due to the later execution. This "drift" in next execution times is characteristic of jobs with simple interval expressions.

Type 2 Jobs

Jobs with type 2 execution requirements involve more complex interval date expressions, as see in the following table.

Action Interval Time
Every day at 12:00 midnight TRUNC(SYSDATE + 1)
Every day at 8:00 p.m. TRUNC(SYSDATE + 1) + 20/24
Every Tuesday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24
First day of the month at midnight TRUNC(LAST_DAY(SYSDATE) + 1)
Last day of the quarter at 11:00 p.m. TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24
Every Monday, Wednesday and Friday at 9:00 p.m. TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 21/24


Job Intervals Examples

Remember that the dbms_job.submit() procedure accepts three parameters: 

DBMS_JOB.SUBMIT (
    job       => :jobno
  , what      => 'statspack.snap;'             -- What to run
  , next_date => TRUNC(sysdate+1/24,'HH')      -- Start next hour
  , interval  => 'TRUNC(SYSDATE+1/24,''HH'')'  -- Run every hour
);           

--  =========================================================
--  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+1)+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)+6/24
      , 'TRUNC(sysdate+10/1440,''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,''MONDAY'')
               , NEXT_DAY(sysdate,''TUESDAY'')
               , NEXT_DAY(sysdate,''WEDNESDAY'')
               , NEXT_DAY(sysdate,''THURSDAY'')
               , NEXT_DAY(sysdate,''FRIDAY'')
             ) + 1/24
        , ''HH'')'
      , TRUE
      , :instno);
 
    COMMIT;

END;
/


Oracle Date Math Examples

Date / Time Fraction Description
WHERE (date) > sysdate - 6/24; Past 6 hours. (or 1/4 of a day ago)
WHERE (date) > sysdate - 6; Past six days
WHERE (date) > sysdate - 6/1440; Past six minutes
6/24
1/4		 6 hours
1/24/60/60
5/24/60/60		 One second
Five 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


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.

If the database is Oracle8i (Release 2) or higher, you can simply use the CASE statement. The following CASE statement returns the correct interval for the above specification: 
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;

SYSDATE
------------------------------

INTERVAL_DATE
------------------------------
sun sep 15, 2002 16:35
mon sep 16, 2002 06:00
The CASE statement gives you great flexibility in generating a complex value such as you need. Unfortunately, DBMS_JOB will allow you to use only an interval that is 200 characters or less, and even if you "scrunch up" the CASE statement above, you'll find it is about 300 characters minimally. So, you cannot use it directly in the call to DBMS_JOB. My solution to that is one of two things: either I would create a view NEXT_DATE as that select, so that select * from next_date would return the next time the job runs, or I would wrap the above query in a PL/SQL function that returns a date. If I used a view, my call to DBMS_JOB might look like: 
begin
 dbms_job.submit
  ( :n, 'proc;', sysdate,
    '(select * from next_date)'
  );
end;
/
Or, if I used the PL/SQL function approach and created a function NEXT_DATE, it could be: 
begin
  dbms_job.submit
  ( :n, 'proc;', sysdate,
    'next_date()'
  );
end;
/


Execute a Job Every xy Hours Starting at an Arbitrary Start Time

The following solution comes thanks to Martin Trappel <tm5@gmx.at>.

This solution combines an arbitrary start date with an arbitrary time-interval. The next execution dates will be exactly n times the interval from the starting date. 

**********

SYS.DBMS_JOB.SUBMIT (
      job       => X 
    , what      => '
begin
  -- do stuff ...
end;
'
    , next_date => :some_date
    , interval  => 'GET_NEXT_JOB_DATE(''my_job'', 2.25)'
    , no_parse  => FALSE
);

**********

FUNCTION GET_NEXT_JOB_DATE(   p_jobid           VARCHAR2
                            , p_interval_hours  NUMBER) RETURN DATE IS
    x_old_next_date DATE;
    x_new_next_date DATE;
BEGIN
    -- FETCH THE CURRENT NEXT DATE OF THE JOB:
    SELECT next_date
    INTO   x_old_next_date
    FROM   user_jobs
    WHERE LOWER(INTERVAL) LIKE '%'||p_jobid||'%';

    IF (x_old_next_date IS NULL) THEN
        x_old_next_date := SYSDATE;
    END IF;

    -- START AT THE LAST SET NEXT DATE AND INCREASE BY THE SET
    -- INTERVAL UNTIL THE NEW NEXT DATE IS IN THE FUTURE:
    x_new_next_date := x_old_next_date;
    WHILE(x_new_next_date <= sysdate)
    LOOP
        x_new_next_date := x_new_next_date + p_interval_hours / 24;
    END LOOP;

    RETURN x_new_next_date;
END GET_NEXT_JOB_DATE;

************

Copyright (c) 1998-2013 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 16-Jun-2006 10:15:50 EDT
Page Count: 107143