From: | "Stijn Vanroye" <s(dot)vanroye(at)Farcourier(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Difference between two times as a numeric value in a stored procedure. |
Date: | 2004-06-04 08:11:57 |
Message-ID: | 736CEAA26E7E3F48943458F760E7A32603B621@fs1010.farcourier.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | hook | 2004-06-04 13:06:15 | trigger data |
Previous Message | Khairul Azmi | 2004-06-04 08:06:32 | ERROR: duplicate key violates unique constraint |