Difference between two times as a numeric value in a stored procedure.
От | Stijn Vanroye |
---|---|
Тема | Difference between two times as a numeric value in a stored procedure. |
Дата | |
Msg-id | 736CEAA26E7E3F48943458F760E7A32603B621@fs1010.farcourier.com обсуждение исходный текст |
Ответы |
Re: Difference between two times as a numeric value in a stored procedure.
Re: Difference between two times as a numeric value in a stored procedure. |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: