 |
|
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. |