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

 
 Home
 E-mail Us
 Oracle Articles
New 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 Scheduling Dates and Times

Oracle Tips by Burleson Consulting

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:

BYTE

MEANING

NOTATION

EXAMPLE
(10-JUL-2004 17:21:30)

1

Century

Divided by 100, excess-100

120

2

Year

Modulo 100, excess-100

104

3

Month

0 base

7

4

Day

0 base

10

5

Hour

excess-1

18

6

Minute

excess-1

22

7

Second

excess-1

31

 

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.

PERIOD

EXPRESSION 1

EXPRESSION 2

EXPRESSION 3

VALUE

1 Day

1

1

1

1

1 Hour

1/24

1/24

1/24

.041666667

1 Minute

1/24/60

1/(24*60)

1/1440

.000694444

1 Second

1/24/60/60

1/(24*60*60)

1/86400

.000011574

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:

DATE FUNCTION

USAGE

sysdate

Returns the current date-time from the operating system of the database server.

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

1 row selected.

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.

select
   to_char(sysdate, ?DD/MM/YY HH24:MI?)
from
   dual;

TO_CHAR(SYSDAT
--------------
10/07/04 18:48

1 row selected.

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.

select
   to_date(?10/07/2004 13:31:45?, ?DD/MM/YYYY HH24:MI:SS?)
from
   dual;

TO_DATE('10/07/2004
-------------------
10/07/2004 13:31:45

1 row selected.

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.

select
   sysdate,
   round(sysdate, ?HH24?)
from
   dual;

SYSDATE             ROUND(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:54:24 10/07/2004 19:00:00

1 row selected.

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.

select
   sysdate,
   trunc(sysdate, ?HH24?)
from
   dual;

SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:55:44 10/07/2004 18:00:00

1 row selected.

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.

FORMAT MODEL

ROUNDING OR TRUNCATING UNIT

CC

SCC

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

select
  sysdate,
  trunc(sysdate, 'CC'),
  round(sysdate, 'CC')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'CC')  ROUND(SYSDATE,'CC')
-------------------- -------------------- --------------------
16-JAN-1999 08:48:09 01-JAN-1901 00:00:00 01-JAN-2001 00:00:00

1 row selected.

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y

To the year. Rounds up on July 1st.

select
  sysdate,
  trunc(sysdate, 'YY'),
  round(sysdate, 'YY')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'YY')  ROUND(SYSDATE,'YY')
-------------------- -------------------- --------------------
08-JUL-2004 08:08:49 01-JAN-2004 00:00:00 01-JAN-2005 00:00:00

1 row selected.

IYYY
IY
IY
I

To the ISO Year.

select
  sysdate,
  trunc(sysdate, 'IY'),
  round(sysdate, 'IY')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'IY')  ROUND(SYSDATE,'IY')
-------------------- -------------------- --------------------
08-JUL-2004 08:10:39 29-DEC-2003 00:00:00 03-JAN-2005 00:00:00

1 row selected.

Q
To the quarter, rounding up on the 16th day of the second month.

select
  sysdate,
  trunc(sysdate, 'Q'),
  round(sysdate, 'Q')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'Q')   ROUND(SYSDATE,'Q')
-------------------- -------------------- -------------------
22-AUG-2004 08:23:56 01-JUL-2004 00:00:00 01-OCT-2004 00:00:00

1 row selected.

MONTH
MON
MM
RM

To the month, rounding up on the 16th day.

select
  sysdate,
  trunc(sysdate, 'MM'),
  round(sysdate, 'MM')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'MM')  ROUND(SYSDATE,'MM')
-------------------- -------------------- --------------------
16-JUL-2004 08:15:31 01-JUL-2004 00:00:00 01-AUG-2004 00:00:00

1 row selected.

WW
To the same day of the week as the first day of the year.

select
  sysdate,
  trunc(sysdate, 'WW'),
  round(sysdate, 'WW')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'WW')  ROUND(SYSDATE,'WW')
-------------------- -------------------- --------------------
12-JUL-2004 08:20:28 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.

IW
To the same day of the week as the first day of the ISO year.

select
  sysdate,
  trunc(sysdate, 'IW'),
  round(sysdate, 'IW')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'IW')  ROUND(SYSDATE,'IW')
-------------------- -------------------- --------------------
16-JUL-2004 08:26:02 12-JUL-2004 00:00:00 19-JUL-2004 00:00:00

1 row selected.

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

select
  sysdate,
  trunc(sysdate, 'W'),
  round(sysdate, 'W')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'W')   ROUND(SYSDATE,'W')
-------------------- -------------------- --------------------
13-JUL-2004 08:28:10 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.

DDD
DD
J

To the day.

select
  sysdate,
  trunc(sysdate, 'DD'),
  round(sysdate, 'DD')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'DD')  ROUND(SYSDATE,'DD')
-------------------- -------------------- --------------------
08-JUL-2004 20:34:24 08-JUL-2004 00:00:00 09-JUL-2004 00:00:00

1 row selected.

DAY
DY
D

To the starting day of the week.

select
  sysdate,
  trunc(sysdate, 'D'),
  round(sysdate, 'D')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'D')   ROUND(SYSDATE,'D')
-------------------- -------------------- --------------------
09-JUL-2004 08:33:01 04-JUL-2004 00:00:00 11-JUL-2004 00:00:00

1 row selected.

HH
HH12
HH24

To the hour.

select
  sysdate,
  trunc(sysdate, 'HH'),
  round(sysdate, 'HH')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:36:22 08-JUL-2004 08:00:00 08-JUL-2004 09:00:00

1 row selected.

MI
To the minute.

select
  sysdate,
  trunc(sysdate, 'MI'),
  round(sysdate, 'MI')
from
  dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:37:32 08-JUL-2004 08:37:00 08-JUL-2004 08:38:00

1 row selected.

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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational