Hi
I am try to create a function that returns true if a timestamp is within working hours.
The function will take the following parameters.
1, timestamp - The timestamp I want to check
2, days_of_week - An array of integers that tells us what days are work days.
3, time_ranges - An array of my custom timerange type. Allows one to pass an array of hours in a day that are not work hours.
3, date_ranges - An array of daterange types. Allows one to pass say date ranges of school holidays.
Something like
CREATE TYPE timerange AS RANGE (
subtype = TIME with time zone
);
CREATE OR REPLACE FUNCTION check_within_working_hours(ts timestamptz, days_of_week integer[], time_ranges timerange[], date_ranges daterange[])
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT extract(dow from $1) = ANY (days_of_week) INTO passed;
IF passed THEN
RETURN passed;
END IF;
return 'f';
END;
$$ LANGUAGE plpgsql;
I have a large table with timestamp ts and double value. I was going to call the function above like
SELECT * FROM sensor_values WHERE check_within_working_hours(ts, '{1,2,3}'::integer[], '{}'::timerange[], NULL) LIMIT 10;
This works but I have a few problems.
I cannot work out how to pass a literal for the array of timerange types.
'{(15:11:21, 18:11:21)}'::timerange[] does not work for example.
Also I can not pass NULL for this parameter I get
ERROR: function check_within_working_hours(timestamp with time zone, integer[], unknown, unknown) is not unique
Once I can pass the parameters I need the sql to check my passed timestamp is within the array of timeranges or dateranges.
Does anyone know what the most efficient means to achieve that is ?
Also should I investigate creating this as a c function or will it be ok performance wise?
Thanks for any advice