Determine difference in Time

[fiat2]

Hello all,
Using Oracle 9.2 and performing this work in a function.

I'm trying to determine if the amount of time elapsed between two timestamps is of a certain value.

whole function below

CREATE OR REPLACE FUNCTION FINISHTIME

(vProdid  		  Prod.ProdID%type,
vWorkstep   	          Route.WorkStep%type)

RETURN VARCHAR2
AS

Entries Number;
varStartTime varChar2(50);
varEndTime varChar2(50);


BEGIN

	SELECT to_char(startdate, 'HH12:MI:SS') 
	INTO varStartTime
	FROM tbl_History
	WHERE prodid = vProdid
	AND workstep = vWorkstep;	
	
	SELECT to_char(sysdate,'HH12:MI:SS') INTO varEndTime FROM dual;
	
   	   IF  to_number((varEndTime) - to_number(varStartTime)) < 1 THEN
 	   	  RETURN 'Y';
	   ELSE
		  RETURN 'N';
	   END IF;
		   
EXCEPTION WHEN NO_DATA_FOUND THEN
	RETURN 'N';
END;
/

Now I have these 2 variables, varStartTime and varEndTime...which contain times, i.e. 8:30 and 11:18.

How can I determine how much time, in hours, has elapsed between these two fields/..?
Thanks!

 

[hoinz]

Don't make it that complicated....
Use data type date for both varStartTime and varEndTime.

Then varEndTime - VarStartTime will be the difference in days,
and (varEndTime - VarStartTime) * 24 will be the difference in hours,
and so on ...

 

[fiat2]

Wow, ok. I feel dumb.

Could you explain the ((v1-v2)*24) and how that translates to time? I just like to know what I am coding...

Thanks alot for you input!!

 

[hoinz]

Sorry, I have to leave just now. (It's evening in my country.)
But it's all in the manual.

 

[jimbopalmer]

There are 86400 seconds in a day (24*60*60)
oracle normally stores dates as a real number, multiplying that number by 86400 gives you the date accurate to seconds. Now subtract the smaller date multiplied by 86400 and you have the seconds between them.

As an aside, I use 86399/86400 a lot, so I often make a global constant g_almost_a_day in my PL/SQL. Trunc(sysdate) is midnight, Trunc(sysdate)+g_almost_a_day is 11:59:59 PM

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

Fiat2,

...and to add a bit more to your understanding, when date arithmetic occurs in Oracle (e.g. <date2> - <date1>), the result is in DAYS. Therefore if the result of the arithmetic is "1.5", then that means 1 1/2 days. If you want to know how many hours that is, then you multiply the 1.5 by 24, which is 36 (hours). Or, you can mulitply 1.5 by 24x60 (1440) for the number of minutes difference; or by 24x60x60 (86400) for the number of seconds.

Also remember that all Oracle DATE expressions contain components (from greatest to least): BC/AD (If century is negative or positive, respectively), Century, Year, Month, Day, Hour, Minute, Second. (Oracle TIMESTAMP expressions can store down to thousandths of second.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]