Calculate elapsed time Use an interval

Question:
I want to write a function that tells me the gap or period of elapsed time between the end of one timesheet record and the beginning of another.
For example:
Start End
05-DEC-05 08:30 05-DEC-05 16:29
06-DEC-05 08:30 06-DEC-05 16:29
Is it better to return a date or a period of elapsed time?
Answer:
The best way to calculate the amount of time between two dates is to take advantage of the INTERVAL and TIMESTAMP datatypes, introduced in Oracle9i Database. The following function takes advantage of these datatypes to provide a function that accepts two dates and returns the interval of time between them:
CREATE OR REPLACE FUNCTION date_diff (
start_date_in IN DATE
, end_date_in IN DATE
)
RETURN INTERVAL DAY TO SECOND
IS
BEGIN
RETURN CAST ( end_date_in AS TIMESTAMP WITH TIME ZONE )
- CAST ( start_date_in AS TIMESTAMP WITH TIME ZONE );
END date_diff;
/
In the rest of this answer, I provide some other examples of working with these types. For more information on intervals and timestamps, check out Chapter 10 of Oracle PL/SQL Programming (O'Reilly) or Oracle's PL/SQL User Guide and Reference, under "PL/SQL Date, Time, and Interval Types."
In the first block below, I have declared two timestamps: boss_free contains the timestamp indicating when my manager is available to talk; steven_leaves contains the date/time when I must depart for my next trip.
To determine the amount of time we have to talk, I declare an INTERVAL datatype, window. I assign the difference between my two timestamps to the interval variable. I then display the interval value (two hours and forty-five minutes) with a call to DBMS_OUTPUT.PUT_LINE.
SQL> DECLARE
2 boss_free TIMESTAMP WITH TIME ZONE;
3 steven_leaves TIMESTAMP WITH TIME ZONE;
4 --
5 window INTERVAL DAY TO SECOND;
6 BEGIN
7 boss_free := TO_TIMESTAMP_TZ (
8 '29-JAN-2002 12:00:00.0 PST',
9 'DD-MON-YYYY HH24:MI:SSXFF TZD' );
10
11 steven_leaves := TO_TIMESTAMP_TZ (
12 '29-JAN-2002 16:45:00.0 CST',
13 'DD-MON-YYYY HH24:MI:SSXFF TZD' );
14
15 window := steven_leaves - boss_free;
16
17 DBMS_OUTPUT.PUT_LINE ( window );
18 END;
19 /
+00 02:45:00.000000
If you are starting with data declared as DATEs, you cannot directly assign a difference of two dates to an interval. Instead, you will need to either:
• Convert the dates to timestamps with the CAST operator
• Convert the difference of the two dates to an interval with the NUMTODSINTERVAL or NUMTOYMINTERVAL built-in functions.
You can also compare the results of a difference between two dates to an interval.
All of these variations are shown in the script below:
SQL> DECLARE
2 boss_free TIMESTAMP WITH TIME ZONE;
3 steven_leaves TIMESTAMP WITH TIME ZONE;
4 --
5 d_boss_free DATE := SYSDATE;
6 d_steven_leaves DATE := SYSDATE + 1.25;
7 --
8 window INTERVAL DAY TO SECOND;
9 BEGIN
10 boss_free := CAST ( d_boss_free AS TIMESTAMP WITH TIME ZONE );
11 steven_leaves := CAST ( d_steven_leaves AS TIMESTAMP WITH TIME ZONE );
12 --
13 window := NUMTODSINTERVAL ( d_boss_free - d_steven_leaves, 'DAY' );
14 DBMS_OUTPUT.put_line ( 'DATE diff = ' || TO_CHAR ( window ));
15 --
16 window := boss_free - steven_leaves;
17 DBMS_OUTPUT.put_line ( 'TS diff = ' || TO_CHAR ( window ));
18
19 --
20 IF ( d_boss_free - d_steven_leaves ) > window
21 THEN
22 DBMS_OUTPUT.put_line ( 'Compare difference of dates to interval!' );
23 END IF;
24
25 --
26 DBMS_OUTPUT.put_line ( 'date_diff = '
27 || TO_CHAR ( date_diff ( steven_leaves, boss_free ))
28 );
29 END;
30 /
DATE diff = -01 06:00:00.000000
TS diff = -01 06:00:00.000000
Compare difference of dates to interval!
date_diff = -01 06:00:00.000000