POSTGRES: problems on functions in workweek
Im newbie on postgres function and im trying to work on a working business
hours using a function and getting the following error when i combine the
two functions
1st function: Get the time in date ranged.
RUN: SELECT f_work('2013-09-13 06:00','2013-09-13 07:00')
Result: 01:00
CREATE OR REPLACE FUNCTION public.f_work (
t_start timestamp,
t_end timestamp
)
RETURNS interval AS
$body$
SELECT (count(*) - 1) * interval '1 min'
FROM (
SELECT $1 + generate_series(0, (extract(epoch FROM $2 - $1)/60)::integer)
* interval '1 min' AS t
) sub
WHERE extract(ISODOW from t) > 0
AND t::time >= '06:00'::time
AND t::time < '19:00'::time
$body$
LANGUAGE 'sql'
2nd function: Convert time in minutes.
RUN: select to_min('01:00')
Result: 60
CREATE OR REPLACE FUNCTION to_min(t text)
RETURNS integer AS
$BODY$
DECLARE
hs INTEGER;
ms INTEGER;
BEGIN
SELECT (EXTRACT(HOUR FROM t::time) * 60) INTO hs;
SELECT (EXTRACT(MINUTES FROM t::time)) INTO ms;
SELECT (hs + ms) INTO ms;
RETURN ms;
END;
$BODY$
LANGUAGE 'plpgsql';
Joining the two function i got and error:
CREATE OR REPLACE FUNCTION f_bizwork(t_start timestamp,t_end timestamp)
RETURNS integer AS
$BODY$
DECLARE
hs INTEGER;
ms INTEGER;
BEGIN
SELECT (count(*) - 1) * interval '1 min'
FROM (
SELECT $1 + generate_series(0, (extract(epoch FROM $2 - $1)/60)::integer)
* interval '1 min' AS t
) sub
WHERE extract(ISODOW from t) >0
AND t::time >= '06:00'::time
AND t::time < '19:01'::time;
SELECT (EXTRACT(HOUR FROM sub::time) * 60) INTO hs;
SELECT (EXTRACT(MINUTES FROM sub::time)) INTO ms;
SELECT (hs + ms) INTO ms;
RETURN ms;
END;
$BODY$
LANGUAGE 'plpgsql';
No comments:
Post a Comment