Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Date Arithmetic Tips

Oracle Tips by Burleson Consulting

April 2. 2012

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))? elapsed days
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.


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.