| Time calculation [message #374564] |
Tue, 19 June 2001 04:14  |
Richard
Messages: 44
Registered: May 2000
|
Member |
|
|
Hi, please excuse me if this seems really simple but...
I've got 2 date fields in a table, example:
date1: 6/12/01 1:46:48 PM
date2: 6/12/01 10:25:00 AM
I'm really struggling to subtract date2 from date1 giving the answer in minutes. The dates may be different, (possibly spanning several weeks) but date2 will ALWAYS be earlier than date1. Eventually, I want to present this as an average for several rows, but right now I don't seem to be able to sort out the basic syntax for the time subtraction...
|
|
|
|
| Re: Time calculation [message #374565 is a reply to message #374564] |
Tue, 19 June 2001 04:38  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
The function Date1-date2 returns the difference in days.
You should be able to multiply this by 1440 (24*60) to get the difference in minutes.
Hope this helps
|
|
|
|
|
|
| Re: Time calculation [message #374583 is a reply to message #374566] |
Tue, 19 June 2001 17:03 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
To concert to HMS.
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
