Developers and DBAs new to Oracle are often perplexed when it comes to working with Oracle dates. Along with the many ways to specify date formats via NLS parameters and the many date functions, there is also the problem of date math.

Working with dates in Oracle can seem somewhat daunting, especially when it comes to adding and subtracting dates, and determining the time of day for a date.

This article will attempt to demystify Oracle dates by providing some understanding of how Oracle stores and manipulates dates, and how to work with Oracle date math.

Let’s begin by getting a little background on how Oracle stores data in the DATE format.

Oracle DATE Format

Oracle dates consist of seven parts: century, year, month of the year, day of the month, and the hours, minutes, and seconds after midnight.

The internal representation of this format can be seen by running the script in Example 1.

alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
col today format a40
drop table t1;
create table t1
as select sysdate today
from dual;
select to_char(today) today
from t1
union
select dump(today) today
from t1;
TODAY
----------------------------------------
04/14/2004 21:59:13
Typ=12 Len=7: 120,104,4,14,22,60,14
2 rows selected.

Example 1: Dumping the internal date format.

The hour, minute, and second are all stored in excess-1 notation, so 1 must be subtracted from them to get the correct time. (Using excess-1 notation prevents a zero byte from being stored.)

The month and day are both stored with the actual value as can be seen in the SELECT output.

The values for the century and year are stored in excess-100 notation. This means that 100 must be subtracted from the value before using it.

In the case of the date in Example 1, the year is clearly seen by subtracting 100 from 104. The century is somewhat different. Not only must 100 be subtracted from the value, it must then be multiplied by 100.

Refer to Example 2 for the formula and an example in SQL.

Please take note if you try this experiment yourself: If you use the SYSDATE or TO_DATE functions rather than actually inserting the date into a table, the results will be quite different. That is because these functions use an external datatype to represent the date, whereas when stored in a table, the date is stored with an internal datatype. The data returned by the function is dependent on the C compiler with which Oracle was compiled. Example 3 demonstrates this.

ts01>delete from t1;

1 row deleted.

ts01>
ts01>insert into t1
  2  values(to_date('2004/01/01 06:42:07','yyyy/mm/dd hh24:mi:ss'))
  3  /

1 row created.

ts01>select
  2          dump(today),
  3          dump(to_date('2004/01/01 06:42:07','yyyy/mm/dd hh24:mi:ss'))
  4  from t1
  5  /

DUMP(TODAY)
----------------------------------------------------------------------------------
DUMP(TO_DATE('2004/01/0106:42:07
--------------------------------
Typ=12 Len=7: 120,104,1,1,7,43,8
Typ=13 Len=8: 212,7,1,1,6,42,7,0

1 row selected.

ts01>

Example 3: Internal and External datatypes.

Regardless of the settings of NLS_DATE_FORMAT, or any conversion applied with date functions such as to_date(), Oracle stores the date in the same manner, always with seven bytes of storage.

Oracle Date Math Granularity

Date math with Oracle can be rather intimidating at first. Adding or subtracting hours, minutes, and seconds to or from a date value appears to be quite complex.

Oracle date math relies on a grain of one day. In other words, the smallest whole unit of time that you can specify in date math is a single day.

Adding or subtracting hours, minutes, and seconds to or from a date requires the use of fractional parts of a day. When you understand that, date math with Oracle becomes much simpler.

Figure 1 illustrates the fractions needed to specify a single unit of several different units of time when adding or subtracting dates.

Figure 1: Specifying portions of a day.

The following examples show how to add or subtract from a date to arrive at a specific time and date:

select sysdate, sysdate + 1/24 from dual

SYSDATE             SYSDATE+1/24
------------------- -------------------
05/15/2004 19:13:30 05/15/2004 20:13:30

Example 4: Add one hour to the current date.

select sysdate, trunc(sysdate + 1/24,'HH24') from dual;

SYSDATE             TRUNC(SYSDATE+1/24,
------------------- -------------------
05/15/2004 19:14:02 05/15/2004 20:00:00

Example 5: Specify the next whole hour of today's date.

alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

define mydate='05/16/2004 20:13:52'
col nextqrtr head 'NEXT QRTR HOUR'

select currdate + mod(trunc(currdate+(1/24),'HH24') - currdate,1/96) nextqrtr
     from (
     select to_date('&&mydate') + 1/86400 currdate
     from dual
)
/

Example 6: Arrive at the next 15-minute period in an hour.

The inline view of Example 6 contains the SQL to put the date defined at the top of the script into date format. After this is done, one second is added. By adding one second to the date and time, the time will correctly increment to the next quarter hour when the time is already at the quarter hour.

For instance, if the current time is 17:45:00, the mod() function in the outer SELECT will not be incremented to 18:00:00 as we would like. Adding one second to the specified date and time corrects this problem.

The outer portion of the SELECT statement determines the next quarter hour by using this formula:

    current date + partial day needed to round to the next quarter hour.

The current quarter hour is determined by this:

    date and time at next whole hour - current date and time

This will yield a number such as .0989. There are 96 quarter hours in a single day (remember that the granule of an Oracle date is one day). Divide this number by .010416667 (the quotient of 1/96) and add the remainder to the current date.

The result will be the next quarter hour. Play with this example by plugging some different values into the mydate substitution variable.

Example 7 may appear somewhat complex. I am including it to demonstrate that even though some date math may appear somewhat complex, it is simple to decipher when you understand how it works. Take a couple of minutes to examine this example and read the comments in the SQL, and you will see clearly that it is much simpler than it first appears to be.

-- add a defined number of days and hours to the
-- current date, and determine how many days, hours
-- minutes and seconds remain until that time and date

define days_to_add=2
define hours_to_add=5

select
   days
   -- get the whole remaining hours
   , trunc(partial_day * 24) hours
   -- get the number of minutes, divide by 60
   -- and the remainder is the number of minutes
   -- less than 1 hour
   , mod(trunc(partial_day * 1440),60) minutes
   -- get the number of seconds, divide by 60
   -- and the remainder is the number of seconds
    -- less than 1 minute
   , mod(trunc(partial_day * 86400),60) seconds
from (
   select
       -- number of days until date
      trunc(timediff) days
      -- size of the remainder - a partial day
      , timediff - trunc(timediff) partial_day
   from (
      -- get the numeric result of the difference between the current
      -- date and time, and the date and time arrived at when the
       -- specified days and hours are added to it
      select trunc(sysdate + &&days_to_add + &&hours_to_add/24,'HH24'
)          - sysdate timediff
      from dual

Example 7: Determine time until a future point in time.

Using date functions with DBMS_JOB

Working with date math is not just for developers. DBA's also must understand it, as when working with the DBMS_JOB built-in job scheduler. Two of the arguments required when submitting a job are the date and time of the initial run, and the interval at which the job should be run.

The SUBMIT procedure of the builtin package DBMS_JOB is used to create a scheduled job in the database.

The specification for DBMS_JOB.SUBMIT is seen in Figure 2. We will concentrate on the use if the first four parameters, JOB, WHAT, NEXT_DATE and INTERVAL. The others are not required for our discussion.

PROCEDURE SUBMIT
Argument Name Type In/Out Default?
----------------- ----------------- ------ --------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT

Figure 2: DBMS_JOB.SUBMIT specification.

The WHAT parameter is the name of the procedure to be executed.
The NEXT_DATE parameter is used to specify the start time for the job, and is usually fairly easy to specify. All that is required is to pass a date that is easily created with the to_date function.

    to_date('07/15/2004 02:00','mm/dd/yyyy hh24:mi')

The INTERVAL can be a little more difficult. It must either be NULL, meaning that the job will not be scheduled to run again, or a PL/SQL expression that evaluates to a future date and time, such as SYSDATE + 7. This would instruct Oracle to next run the job seven days from now.

Say you have a procedure that removes history records from an archival table in your application. The business has determined that these records should be cleaned up once a week. All rows older than 14 days will be removed from the table. You are allowed a window of 01:00 — 01:30 on Monday mornings to run this job. “No problem,” you think, as the job only takes five minutes to run to completion.

Example 8 shows one way to do this via sqlplus.

variable my_job number;
begin
dbms_job.submit(
:my_job,
'archive_rec_cleanup',
to_date('05/24/2004 01:00','mm/dd/yyyy hh24:mi'),
'sysdate+7'
);
end;
/
print my_job

Example 8: Archival cleanup.

Your job is working perfectly the first few weeks, but on the seventh week, you find an email in your inbox on Monday morning, asking you to find out why the archival cleanup didn’t complete until 01:35.

The problem here is that DBMS_JOB does not schedule the next iteration of the job until the current one completes. When you recall that the job requires five minutes to run, it all makes sense. Each job was scheduled to begin five minutes later than the previous one.

If you let R=runtime, S=start time, and N=next run time, then N = S + R will give you the starting time of each successive job, as can be seen in Figure 3.

Figure 3: Archival cleanup run times.

What you actually wanted was to start the job at the same time each week. One way to accomplish that is demonstrated in Example 9.

variable my_job number;
begin
  dbms_job.submit(
    :my_job,
  'archive_rec_cleanup',
  to_date('05/24/2004 01:00','mm/dd/yyyy hh24:mi'),
    'trunc(sysdate+7) + (1/24)'
  );
end;
/
print my_job

Example 9: Archival cleanup.

By using trunc(sysdate+7), an explicit and non-varying time of day is chosen. It will always be at the beginning of the day, seven days from the current dates. The default behavior for truncating dates is to truncate to the beginning of the day. This could be explicitly expressed as trunc(sysdate+7,'DD').

By adding (1/24) to the date, the time will always be 01:00 in the morning, seven days from the current date.

Example 10 shows how to specify several different intervals for DBMS_JOB.SUBMIT:

Every hour on the hour, beginning at the next hour.

begin
  dbms_job.submit(
    :my_job,
	 'archive_rec_cleanup',
	 -- every hour, on the hour
	 trunc(sysdate,'HH24') + (1/24),
    'trunc(sysdate,'HH24) + (1/24)'
  );
end;
/

Every quarter hour, beginning at 05:00 tomorrow.

begin
  dbms_job.submit(
    :my_job,
	 'archive_rec_cleanup',
	 trunc(sysdate+1,) + (5/24),
	 -- run at the quarter hour
    '(sysdate+1/86400)
       + mod(trunc(sysdate+(1/86400)+(1/24),''HH24'')
       - (sysdate+1/86400),1/96)'
  );
end;
/

At 3:00 in the morning only on the the 3rd day of the month each month, 
beginning next month.

begin
  dbms_job.submit(
    :my_job,
	 'archive_rec_cleanup',
	 -- 3:00 am on the third of the month
    trunc(add_months(trunc(sysdate),1),'mon') + 2 + (3/24),
    'trunc(add_months(trunc(sysdate),1),''mon'') + 2 + (3/24)'
  );
end;
/

The first Monday following the first of the month.

begin
  dbms_job.submit(
    :my_job,
	 'archive_rec_cleanup',
	 -- first Monday following the first of the month
	 -- ie. if Monday is on the first, don't run until the 8th
    next_day(trunc(add_months(trunc(sysdate),1),'mon')  + (3/24),'monday'),
    'next_day(trunc(add_months(trunc(sysdate),1),''mon'')  + (3/24),''monday'')'
  );
end;
/

Example 10: Interval examples.

You can become very creative with Oracle date functions when it comes to setting up recurring jobs via the DBMS_JOB package. On occasion it may be simpler to schedule multiple jobs via DBMS_JOB rather than trying to create a date expression that fits all occurrences of a job.

For example, you may be able to write a single date expression that runs a job at 05:00 on Monday, 05:30 on Wednesday, 06:00 on Friday, and does so only during the first full week of the month. However, while it may be possible, it may not be practical.

Scheduling three different jobs may be much easier to maintain, as the date expression required to do so as a single job is likely to be difficult to follow.

As always, you should create comments when you schedule your jobs; it will make it much easier to read later.

Though these methods of working with dates have been valid since at least Version 7 of Oracle, they still seem to be a source of confusion for many. These exercises should help with your understanding of date math.

Oracle 9 adds even more date-related features, such as intervals and time zones. We can delve into those features at another time.

--

Jared Still is a DBA at RadiSys, an embedded solution provider. Jared has been an IT professional for 22 years, the last 10 of which have been as an Oracle DBA. He is the author of several Oracle articles and co-author of Perl for Oracle DBAs as well as being an editor for the IOUG Journal SELECT.