Hello List,
I am writing two stored procedure which alternatively returns the dayhours and
nighthours of two times. (nighthours are considered between 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 time values (or timestamp values) and get a
numeric/float value. I always get the INTERVAL datatype. For example, in stead of 4:30
i would like 4.5 as a result. I have searched the documentation but could not find any
way to substract time/timestamp values and get a numeric/float as a result. When I try
to CAST the interval to a numeric or float value I get an error (cannot cast time
without tz to ...). Same goes for trying to cast the beginvalues and then substract
them. Does anyone 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
