| Re: How to Sum Times [message #375202] |
Fri, 03 August 2001 13:46 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
The trick is to convert each time to a date using to_date(). Whatever the date portion used is irrelevant - use 01-Jan-1980 or just the date portion from sysdate. Once they are in date datatype, use usual date arithmetic to get the time differences between each date, add the answers (expressed in fractions of days) together and then display the result as in HH:MI:SS. See the following eaxmple for ideas:
Based on http://www.orafans.com/ubb/Forum6/HTML/002352.html
CREATE OR REPLACE FUNCTION to_hms (v_days IN number)
RETURN varchar2
IS
v_retval varchar2(15);
BEGIN
v_retval := TO_CHAR (TRUNC (v_days)) ||
TO_CHAR (TRUNC (SYSDATE) + MOD (v_days, 1), ' HH24:MI:SS');
RETURN v_retval;
END to_hms;
/
select to_date('26-Jul-2000 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('20-Jul-2000 11:05:05', 'dd-Mon-yyyy hh24:mi:ss') from dual;
6.09392361111111
select to_hms(to_date('26-Jul-2000 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('20-Jul-2000 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from dual;
6 02:15:15
(6 days, 2 hrs, 15 min, 15 sec)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
