Oracle Scheduling Dates and Times

For complete details on Oracle date manipulation, see Dr. Hall's book "Oracle Job Scheduling" by Rampant TechPress.

Take a closer look at Dates and Times

In order to understand how repeat intervals of jobs are specified, a review of how Oracle handles date-time information is warranted.  This chapter will present information on how to use dates, timestamps and intervals along with the Oracle10g calendar syntax to define repeat intervals for scheduled jobs.

The use of the DATE datatype used by the dbms_job package will be examined in the following section.

Oracle Date arithmetic

The DATE datatype is used by Oracle to store all datetime information in which a precision greater than one second is not needed.  Oracle uses a seven byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-4712 AD.  Table 3.1 shows how each of the seven bytes is used to store the date information:

The following example uses the dump function to show the contents of a stored date:

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

drop table date_test;

create table date_test as
select
   sysdate as now
from
   dual
;

select
   now,
   dump (now)
from
   date_test
;

NOW
-------------------
DUMP(NOW)
-----------------------------------------
10-JUL-2004 17:21:30
Typ=12 Len=7: 120,104,7,10,18,22,31

1 row selected.

When comparing the date and dump values, subtracting 100 from the century component, then multiplying the resulting value by 100 gives a value of 2000.  Subtracting the 100 from the year component gives a value of 4.  The month and day components need no modification, while subtracting one from the hour, minute and second components (18, 22 and 31) give values of 17, 21 and 30.

Jobs scheduled using the dbms_job package use dates to define time related information, as seen in the dbms_job.submit procedure and the dba_jobs view shown below.

PROCEDURE submit (
  job       OUT BINARY_INTEGER,
  what      IN  VARCHAR2,
  next_date IN  DATE DEFAULT sysdate,
  interval  IN  VARCHAR2 DEFAULT 'null',
  no_parse  IN  BOOLEAN DEFAULT FALSE,
  instance  IN  BINARY_INTEGER DEFAULT 0,
  force     IN  BOOLEAN DEFAULT FALSE)

SQL> describe dba_jobs
 Name                             Null?    Type
 -------------------------------- -------- -------------------------
 JOB                              NOT NULL NUMBER
 LOG_USER                         NOT NULL VARCHAR2(30)
 PRIV_USER                        NOT NULL VARCHAR2(30)
 SCHEMA_USER                      NOT NULL VARCHAR2(30)
 LAST_DATE                                 DATE
 LAST_SEC                                  VARCHAR2(8)
 THIS_DATE                                 DATE
 THIS_SEC                                  VARCHAR2(8)
 NEXT_DATE                        NOT NULL DATE
 NEXT_SEC                                  VARCHAR2(8)
 TOTAL_TIME                                NUMBER
 BROKEN                                    VARCHAR2(1)
 INTERVAL                         NOT NULL VARCHAR2(200)
 FAILURES                                  NUMBER
 WHAT                                      VARCHAR2(4000)
 NLS_ENV                                   VARCHAR2(4000)
 MISC_ENV                                  RAW(32)
 INSTANCE                                  NUMBER

It is also the data type that must be returned by the expression defined in the interval parameter of jobs scheduled using the dbms_job package.

Since dates are actually numbers, certain simple mathematical operations can be performed on them.  Adding a whole number to a date is like adding the equivalent number of days, while adding a fraction to a date is like adding that fraction of a day to the date.  The same is true in reverse for subtraction.  Table 3.2 below shows how each specific time period can be calculated.  All three expressions equate to the same value, allowing the DBA to pick a preferred method.

The following query shows how these expressions might be used to modify the value of the current operating system date:

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

select
   sysdate as current_date,
   sysdate + 1 as plus_1_day,
   sysdate + 2/24 as plus_2_hours,
   sysdate + 10/24/60 as plus_10_minutes,
   sysdate + 30/24/60/60 as plus_30_seconds
from
   dual;

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

The results of this query are listed below.

CURRENT_DATE        PLUS_1_DAY          PLUS_2_HOURS        PLUS_10_MINUTES     PLUS_30_SECONDS
------------------- ------------------- ------------------- --------

10/07/2004 17:57:30 11/07/2004 17:57:30 10/07/2004 19:57:30 10/07/2004 18:07:30 10/07/2004 17:58:00

1 row selected.

Oracle provides several date functions to make date manipulation simpler.  Table 3.3 below lists a selection of those date functions and the examples of their usage:

select
   sysdate
from
   dual;

SYSDATE
-------------------
10/07/2004 18:34:12

1 row selected.

current_date

Similar to the sysdate function, but returns the current date-time within the session?s time zone.

select
   current_date
from
   dual;

CURRENT_DATE
-------------------
10/07/2004 18:36:24

1 row selected.

add_months(date, months)

Adds or subtracts the specified number of months from the specified date.

select
   sysdate,
   add_months(sysdate, 2)
from
   dual;

SYSDATE             ADD_MONTHS(SYSDATE,
------------------- -------------------
10/07/2004 18:40:46 10/09/2004 18:40:46

1 row selected.

last_day(date)

Returns the last day of the month that contains the specified date.

select
   sysdate,
   last_day(sysdate)
from
   dual;

SYSDATE             LAST_DAY(SYSDATE)
------------------- -------------------
10/07/2004 18:42:14 31/07/2004 18:42:14

1 row selected.

next_day(date, day)

Returns the date of the first day that matches the specified day that occurs after the specified date.

select
   sysdate,
   next_day(sysdate, ?MONDAY?)
from
   dual;

SYSDATE             NEXT_DAY(SYSDATE,'M
------------------- -------------------

10/07/2004 18:43:44 12/07/2004 18:43:44

1 row selected.

new_time(date, timezone1, timezone2)

Converts a date from timezone1 into the appropriate date for timeszone2.

select
   sysdate,
   new_time(sysdate, ?GMT?, ?EST?)
from
   dual;

SYSDATE             NEW_TIME(SYSDATE,'G
------------------- -------------------
10/07/2004 18:46:12 10/07/2004 13:46:12

to_char(date, format)

Converts a specified date to a string using the specified format mask.  If the format mask is omitted, the nls_date_format value is used.  There is also an overload of this function to deal with timestamps where the default format mask is taken from the nls_timestamp_format or nls_timestamp_tz_format  value.

to_date(date_string, format)

Converts a specified string to a date using the specified format mask.  If the format mask is omitted, the nls_date_format value is used.

round(date, format)

Returns a date rounded to the level specified by the format.  The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date or the following date depending on the rounding.

trunc(date, format)

Returns a date truncated to the level specified by the format.  The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date.  Using the trunc function allows comparison of dates without the time components distracting from the true meaning of the comparison.  It is similar to the round function, except that it always rounds down.

This code computes dates six months apart, and retrieves data between dates having 6 months difference:

select
   username,
   callcharge
from
   bill
where
   to_char(callend,'DD-MON-YY')>= to_date('&MIN_DATE')
and
   to_char(callend,'DD-MON-YY')<= to_date('&MAX_DATE');

The round and trunc functions can be especially useful; therefore, their format models will be included in more detail.  Table 3.4 below lists the format models, their meanings and examples of their usage.  The dates have been adjusted, where necessary, to show the difference between the return values of the functions.

To the first year of the century (1901, 2001, 2101 etc.)

W
To the same day of the week as the first day of the month.

To the day.

Table 3.4 - Format models, their meanings and examples of usage

This introduction to the DATE datatype is a good foundation for the information presented on the TIMESTAMP datatype in the following section.  The TIMESTAMP datatype is similar to the DATE datatype in many ways.

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.