Thread: Difference between two times as a numeric value in a stored procedure.

Hello List,

I am writing two stored procedure which alternatively returns the dayhours and nighthours of two times. (nighthours are
consideredbetween 00:00 and 06:00). 

As an example here is the getdayhours function:
------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.getdayhours(time, time) RETURNS interval AS
'DECLARE begintime ALIAS FOR $1; endtime ALIAS FOR $2; begindate timestamp; enddate timestamp; tmpresult interval;
BEGIN IF endtime = time \'00:00\' THEN    enddate := (current_date+1)+endtime; ELSE     enddate :=
current_date+endtime;END IF; IF begintime < time \'06:00\' THEN   begindate := current_date + time \'06:00\'; ELSE
begindate:= current_date+begintime; END IF; tmpresult := enddate-begindate; IF tmpresult<\'00:00\' THEN    return
\'00:00\';ELSE   return tmpresult; END IF; 
END;' LANGUAGE 'plpgsql' VOLATILE;
------------------------------------------------------------------------------------------------

The working of the functions is not the problem, but the return type is. I can't seem to find a way to substract two
timevalues (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. For example, in
steadof 4:30 i would like 4.5 as a result. I have searched the documentation but could not find any way to substract
time/timestampvalues and get a numeric/float as a result. When I try to CAST the interval to a numeric or float value I
getan error (cannot cast time without tz to ...). Same goes for trying to cast the beginvalues and then substract them.
Doesanyone have any idea how I can solve/circumvent this problem? Is there a function I can use? 

I don't know if it helps but I'm going to use the functions like this:
SELECT workhour_id, employee_id, task_id, whdate, begintime, endtime, getdayhours(begintime,endtime),
getnighthours(begintime,endtime)FROM workhour 


Thanks in advance.

Stijn Vanroye

On Fri, Jun 04, 2004 at 10:11:57 +0200, Stijn Vanroye <s.vanroye@Farcourier.com> wrote:
> The working of the functions is not the problem, but the return type is. I can't seem to find a way to substract two
timevalues (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. For example, in
steadof 4:30 i would like 4.5 as a result. I have searched the documentation but could not find any way to substract
time/timestampvalues and get a numeric/float as a result. When I try to CAST the interval to a numeric or float value I
getan error (cannot cast time without tz to ...). Same goes for trying to cast the beginvalues and then substract them.
Doesanyone have any idea how I can solve/circumvent this problem? Is there a function I can use?
 

You can use EXTRACT to extract the EPOCH from the interval which will
be the number of seconds in the interval. You can then use arithmetic
(/60.) on that number to get fractional hours.

"Stijn Vanroye" <s.vanroye@Farcourier.com> writes:
> I can't seem to find a way to substract two time values (or
> timestamp values) and get a numeric/float value. I always get the
> INTERVAL datatype.

extract(epoch from interval) may help.
        regards, tom lane