Working with Oracle Dates[1]

by Jared Still

 

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.

 

Day1
Hour1/24
Minute1/1440
Second1/86400

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.

 

 

WeekS R N
101:00 00:0501:05
201:0500:0501:10
301:1000:0501:15
401:1500:0501:20
501:2000:0501:25
601:2500:0501:30
701:3000:0501:35

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.