Overview
If you store date and time information
in Oracle, you have two different options for the column's datatype -
DATE and TIMESTAMP.
DATE is the datatype that we are all
familiar with when we think about representing date and time values. It
has the ability to store the month, day, year, century, hours, minutes,
and seconds. It is typically good for representing data for when
something has happened or should happen in the future. The problem
with the DATE datatype is its' granularity when trying to determine
a time interval between two events when the events happen within a
second of each other. This issue is solved with the TIMESTAMP datatype.
In order to represent the date stored in a more readable format, the
TO_CHAR function has traditionally been wrapped around the date:
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS')
"hiredate"
FROM emp;
hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00
You can add and subtract number
constants as well as other dates from dates. Oracle interprets number
constants in arithmetic date expressions as numbers of days.
For example:
- SYSDATE + 1
is tomorrow
- SYSDATE - 7
is one week ago
- SYSDATE +
(10/1440) is ten minutes from now.
Subtracting the HIREDATE column of the
EMP table from SYSDATE returns the number of days since each employee
was hired.
SELECT '03.12.2004:10:34:24' "Now",
TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
- hiredate "Hired since [Days]"
FROM emp;
Now Hiredate Hired since [Days]
------------------- ------------------- ------------------
03.12.2004:10:34:24 17.12.1980:00:00:00 8752.44056
You cannot multiply or divide DATE
values. Oracle provides functions for many common date operations. For
example, the ADD_MONTHS function lets you add or subtract months from a
date. The MONTHS_BETWEEN function returns the number of months between
two dates.
Subtraction between Dates
The trouble people get into when using
the DATE datatype is doing arithmetic on the column in order to figure
out the number of years, weeks, days, hours, and seconds between two
dates. What needs to be realized when doing the calculation is that
when you do subtraction between dates, you get a number that
represents the number of days. You should then multiply that number
by the number of seconds in a day (86400) before you continue with
calculations to determine the interval with which you are concerned.
DEFINE Today =
TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60))
"Sec",
trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60))
"Min",
trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24))
"Hrs",
trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;
Hiredate
Today
Sec Min Hrs Days
------------------- ------------------- --- --- --- -----
17.12.1980:00:00:00 03.12.2004:10:34:24 24 34
10 8752
Check out the above
query for a possible solution on how to extract the individual time
intervals for a subtraction of two dates. The fractions could be
reduced but we wanted to show all the numbers to emphasize the
calculation.
If you want a solution
which breaks the days in years and month you can use the following
query. We will use a leap year date, 01/01/2000 for example, for
temporary purposes. This date will provide accurate calculation for
most cases.
DEFINE DateDay =
8752.44056
SELECT
TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
+ &DateDay,'YYYY MM DD HH24:MI:SS') A
FROM DUAL);
YEARS
MONTHS DAYS HO MI SE
---------- ---------- ---------- -- -- --
23
11 17 10 34 24
The new TIMESTAMP datatype
One of the main problems with the DATE
datatype was its' inability to be granular enough to determine which
event might have happened first in relation to another event. Oracle
has expanded on the DATE datatype and has given us the TIMESTAMP
datatype which stores all the information that the DATE datatype
stores, but also includes fractional seconds.
Convert DATE datatype to
TIMESTAMP datatype
If you want to convert a DATE datatype
to a TIMESTAMP datatype format, just use the CAST function. As you can
see, there is a fractional seconds part of '.000000' on the end of this
conversion. This is only because when converting from the DATE datatype
that does not have the fractional seconds it defaults to zeros and the
display is defaulted to the default timestamp format
(NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from
one table to a TIMESTAMP datatype column of another table, all you need
to do is a INSERT SELECT FROM and Oracle will do the conversion for you.
CREATE TABLE date_table
(
date1 DATE,
time1 TIMESTAMP,
time2 TIMESTAMP
);
INSERT INTO date_table (date1, time1, time2)
VALUES (SYSDATE,
TO_TIMESTAMP
('17.12.1980:00:00:00','DD.MM.YYYY:HH24:MI:SS'),
TO_TIMESTAMP
('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
);
COMMIT;
SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;
Date
---------------------------------------------------------------------------
03-DEC-04 11.36.45.000000 AM
The TO_TIMESTAMP function
The TO_TIMESTAMP
function converts a string to a timestamp. The syntax for the
to_timestamp function is:
TO_TIMESTAMP (
string , [ format_mask ] [ 'nlsparam' ] )
string is the
string that will be converted to a timestamp.
format_mask is optional. This is the format that
will be used to convert string to a timestamp.
The following is a
list of options for the format_mask parameter These
parameters can be used in many combinations.
|
Parameter
|
Explanation
|
|
YYYY
|
4-digit year
|
|
MM
|
Month (01-12;
JAN = 01).
|
|
MON
|
Abbreviated name
of month.
|
|
MONTH
|
Name of month,
padded with blanks to length of 9 characters.
|
|
DD
|
Day of month
(1-31).
|
|
HH
|
Hour of day
(1-12).
|
|
HH12
|
Hour of day
(1-12).
|
|
HH24
|
Hour of day
(0-23).
|
|
MI
|
Minute (0-59).
|
|
SS
|
Second (0-59).
|
Formatting of the
TIMESTAMP datatype
Formatting of the new TIMESTAMP
datatype is the same as formatting the DATE datatype. Beware while the
TO_CHAR function works with both datatypes, the TRUNC function will
not work with a datatype of TIMESTAMP. This is a clear indication
that the use of TIMESTAMP datatype should explicitly be used for date
and times where a difference in time is of utmost importance, such that
Oracle won't even let you compare like values. If you wanted to show
the fractional seconds within a TIMESTAMP datatype, look at the 'FF3'
to only showing 3 place holders for the fractional seconds.
Formatting of the TIMESTAMP datatype:
SELECT
TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date"
FROM date_table;
Date
-------------------
12/17/1980 00:00:00
Formatting of the TIMESTAMP datatype
with fractional seconds:
SELECT
TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date"
FROM date_table;
Date
-----------------------------
12/17/1980 00:00:00:000
Subtraction of two TIMESTAMP
datatypes
Calculating the time difference between
two TIMESTAMP datatypes is much easier than the old DATE datatype.
Look at what happens when you just do the same substraction as in the
above queries:
SELECT
SUBSTR(time1,1,30) "Time1",
SUBSTR(time2,1,30) "Time2",
SUBSTR((time2-time1),1,30) "Time1
- Time2"
FROM date_table;
Time1
Time2
Time1 - Time2
------------------------------ ------------------------------
---------------------------
17-DEC-80 12.00.00.000000 AM 03-DEC-04 10.34.24.000000
AM +000008752 10:34:24.000000
As you can see, the results are much
easier to recognize, 8752 days, 10 hours, 34 minutes, and 24 seconds.
This means no more worries about how many seconds in a day and all
those cumbersome calculations. And therefore the calculations for
getting the weeks, days, hours, minutes, and seconds becomes a matter
of picking out the number by using the SUBSTR function as can be seen
next:
SELECT
SUBSTR(time1,1,30) "Time1",
SUBSTR(time2,1,30) "Time2",
SUBSTR((time2-time1),
INSTR((time2-time1),' ')+7,2) "SS",
SUBSTR((time2-time1),
INSTR((time2-time1),' ')+4,2) "MI",
SUBSTR((time2-time1),
INSTR((time2-time1),' ')+1,2) "HH",
TRUNC(TO_NUMBER(SUBSTR((time2-time1),1, INSTR(time2-time1,' ')))) "Days"
FROM date_table;
Time1
Time2
SS MI HH Days
------------------------------ ------------------------------ -- -- --
----------
17-DEC-80 12.00.00.000000 AM 03-DEC-04 10.34.24.000000
AM 24 34 10 8752
NEXT_DAY and LAST_DAY functions
The NEXT_DAY and LAST_DAY functions can
be used to calculate for example «the last Saturday in any given
month». You can simply get the last day in the month, subtract 7
days from that, and then use NEXT_DAY to find the next Saturday after
that one.
NEXT_DAY (date, char)
NEXT_DAY returns the date of the first
weekday named by char that is later than date. The
return type is always DATE, regardless of the datatype of date.
The argument char must be a day of the week in the date language of
your session, either the full name or the abbreviation. The minimum
number of letters required is the number of letters in the abbreviated
version. Any characters immediately following the valid abbreviation
are ignored. The return value has the same hours, minutes, and seconds
component as the argument date.
Example
Return the date of the next Monday
after now:
SELECT
TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"
FROM DUAL;
Next Monday
-----------
06.12.2004
LAST_DAY(date)
LAST_DAY returns the date of the last
day of the month that contains date. The return type is always
DATE, regardless of the datatype of date.
Example
The following statement determines how
many days are left in the current month:
SELECT
SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
SYSDATE
Last Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04 28
Get the last date
of a month:
SELECT
LAST_DAY (TO_DATE ('02','MM')) FROM dual;
LAST_DAY
---------
29-FEB-04
Return
the last Saturday of each month for a given year
You can simply get
the last day in the month, subtract 7 days from that, and then use
NEXT_DAY to find the next Saturday after that one.
DEFINE
my_month = 12;
SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY'
),'DAY')
),'DD.MM.YYYY') "Last Saturday in December 2004"
FROM dual;
Last Saturday in December 2004
------------------------------
25.12.2004
Return the last
Saturdays for the current year.
SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (
ADD_MONTHS
(TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
TO_CHAR
(TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
), 'DD.MM.YYYY') "Last Saturdays in 2004"
FROM ALL_OBJECTS
WHERE ROWNUM <= 12;
Last Saturdays in 2004
----------------------
31.01.2004
28.02.2004
27.03.2004
24.04.2004
29.05.2004
26.06.2004
31.07.2004
28.08.2004
25.09.2004
30.10.2004
27.11.2004
25.12.2004
The "29-01-1927" is just a random date
that we knew was a Saturday—any Saturday would do. This is done instead
of using "SAT" in the query for international reasons, because in
languages other than English, "SAT" isn't Saturday. This query should
work in any language out there.
Nice Function to return a Difference between Dates
CREATE OR REPLACE
Procedure
print_date_diff(p_dte1 IN DATE, p_dte2 IN DATE, v_result OUT varchar2)
IS
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
BEGIN
v_diff :=
ABS(p_dte2 - p_dte1);
v_hrs :=
TRUNC(v_diff, 0)*24; -- start with days portion if any
v_diff := (v_diff
- TRUNC(v_diff, 0))*24; -- lop off whole days, convert to hrs
v_hrs := v_hrs +
TRUNC(v_diff, 0); -- add in leftover hrs if any
v_diff := (v_diff
- TRUNC(v_diff, 0))*60; -- lop off hrs, convert to mins
v_min :=
TRUNC(v_diff, 0); -- whole mins
v_sec :=
TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0); -- lop off mins,
convert to secs
v_result :=
TO_CHAR(v_hrs) || ' HRS ' || TO_CHAR(v_min) || ' MIN ' || TO_CHAR(v_sec) || ' SEC';
DBMS_OUTPUT.put_line(v_result);
END print_date_diff;
/
Getting the Difference Between 2 Dates
set
serveroutput on
declare
a date;
b date;
begin
a := sysdate;
dbms_lock.sleep(10); -- sleep about 10 seconds give
or take
b := sysdate;
dbms_output.put_line( b-a || ' of a day has elapsed' );
dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
end;
/
.000127314814814814814814814814814814814815 of a day has elapsed
.00305555555555555555555555555555555555556 of an hour has elapsed
.1833333333333333333333333333333333333336 of a minute has elapsed
11.00000000000000000000000000000000000002 seconds has elapsed
To break the diff between 2 dates into days, hours, minutes,
sec
-- you can use the following:
select to_char( created, 'dd-mon-yyyy
hh24:mi:ss' ),
trunc(
sysdate-created ) "Dy",
trunc( mod(
(sysdate-created)*24,
24 ) ) "Hr",
trunc( mod(
(sysdate-created)*24*60,
60 ) ) "Mi",
trunc( mod( (sysdate-created)*24*60*60, 60 ) )
"Sec",
to_char( sysdate,
'dd-mon-yyyy
hh24:mi:ss' ),
sysdate-created
"Tdy",
(sysdate-created)*24
"Thr",
(sysdate-created)*24*60 "Tmi",
(sysdate-created)*24*60*60
"Tsec"
from all_users
where rownum < 50
/
|