Oracle Date Arithmetic Tips Oracle Database Tips by Donald BurlesonApril 2. 2015 - Revised March 12, 2016

Question:  How to I compare the difference between two dates in Oracle SQL?  I also want to know how to perform complex date arithmetic.

Answer:  Oracle SQL offers a DATE datatype from which you can do basic date arithmetic, determining the difference (in days) between two dates. One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time.

When date values are compared in the WHERE clause, the format of the date must match the format that the database is using or the comparison will fail.  Alternately, if you are using another format, then you must tell the database how your date is formatted.   The default date format that the Oracle database uses is:  DD-Mon-YY.

Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates.  Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds. 

Also note SQL to convert a day of the week to a number.

For learning date arithmetic, we can use the Oracle "dual" pseudo table for date arithmetic. This last query displays the day of the week for any date in the past 1,000 years. To see the day of the week that you were born, copy this query into your c: directory, add your birth date, and run the query see what day of the week you were born on.

select
to_char(to_date('25-MAR-1956','dd-mon-yyyy'),'day')
from dual;

TO_CHAR(T
---------
sunday

You can perform date arithmetic directly in SQL*Plus, doing the math right in the SQL: 

SQL> SELECT
  2    SYSDATE Today,
  3    SYSDATE - 1 Yesterday,
  4    SYSDATE + 1 Tomorrow
  5  FROM
  6    dual;

TODAY     YESTERDAY TOMORROW
--------- --------- ---------
23-JAN-05 22-JAN-05 24-JAN-05

As you can see, the standard unit in Oracle date arithmetic is one day.  When you add time to the date with SQL updates, you do it in fractions of a day.

1 Day              1               1          1
1 Hour             1/24            1/24       0.0417
1 Min              1/(24x60)       1/1440     .000694
1 Sec              1/(24x60x60)    1/86400    .000011574

The notation in the second column is most commonly used, because it is so much easier to read.  Five minutes is 5/(24x60), much easier than 5/1440 or .00347.  When we get to date functions in Chapter 2, you will see that there are functions to do date math by months, weeks and so forth.

When performing Oracle date arithmetic, it might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:

round(to_number(end-date-start_date)*24)- elapsed hours
round(to_number(end-date-start_date)*1440)- elapsed minutes 

How is Oracle elapsed time data displayed by default-  To find out, we issue a simple SQL*plus query:
 
SQL> select sysdate-(sysdate-3) from dual;
 
SYSDATE-(SYSDATE-3)
-------------------
                  3
Here we see that elapsed times are expressed in days.  Hence, we can use easy conversion functions to convert this to hours or minutes.  However, when the minutes are not a whole number, we have the problem of trailing decimal places:
 

select
   (sysdate-(sysdate-3.111))*1440
from
   dual;
 
 
(SYSDATE-(SYSDATE-3.111))*1440
------------------------------
                    4479.83333

Of course, we can overcome this with the ROUND function with Oracle SYSDATE, remembering that we must first convert the DATE datatype to a NUMBER type:

select
   round(to_number(sysdate-(sysdate-3.111))*1440)
from
   dual;
 
ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)
----------------------------------------------
                                          4480

We can use these Oracle SYSDATE functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table.  In this example, we have a Oracle logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:

update
   perfstat.stats$user_log
set
   elapsed_minutes =
   round(to_number(logoff_time-logon_time)*1440)
where
   user = user_id
and
   elapsed_minutes is NULL;

Here is the Oracle date arithmetic to get the list of dates for the day between the 2 dates.

SQL> SELECT
2 SYSDATE Today,
3 SYSDATE - 1 Yesterday,
4 SYSDATE + 1 Tomorrow
5 FROM
6 dual;

TODAY YESTERDAY TOMORROW
--------- --------- ---------
23-JAN-05 22-JAN-05 24-JAN-05

Oracle date arithmetic with intrinsic functions

You can use intrinsic Oracle functions to determine any date in the future:
 

Date / Time Math	Time Description
WHERE (date) > sysdate - 7/24;	Past 7 hours
WHERE (date) > sysdate - 7;	Past 7 days
WHERE (date) > sysdate - 7/1440;	Past 7 minutes
7/24 13/44	7 hours 13 hours
1/24/60/60 7/24/60/60	One second Seven 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

For full examples of using Oracle data functions for scheduling, see Dr. Hall's book "Oracle Job Scheduling":

--  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)+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/144,'MI'),

   'trunc(sysdate+1/144,''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 - 1,'?MONDAY'?),
   next_day(SYSDATE - 1,''tUESDAY'?),
   next_day(SYSDATE - 1,'?WEDNESDAY'?),
   next_day(SYSDATE - 1,''tHURSDAY'?),
   next_day(SYSDATE - 1,'?FRIDAY'?)
)
+1/24,'?HH'?)',
TRUE,
:instno);

commit;
end;
/

---

Author Jeff Hunter also has examples of Oracle date math scheduling:

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.

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;

 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.