I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is going on. I looked into this post and keep having alternating errors like
psql:ERROR: operator does not exist: time without time zone >= timestamp with time zone
and
plpgsql: No function matches the given name and argument types. You might need to add explicit type casts
Can anyone enlighten me what is wrong and what could be a direction I can head towards?
create or replace function riders_available_at_date_time(specified timestamp)
returns table(riderId integer) as
$$
begin
select F.riderId
from FullTimeRiders F
where specified::date between F.startDate and F.startDate+27
and exists (
select 1
from MWSWorkDays M join ShiftsInformation S on (S.shiftNumber = M.shift)
where M.riderId = F.riderId
and (specified::time without time zone between to_timestamp(to_char(firstStartHour, '99'), 'HH24')
and to_timestamp(to_char(firstEndHour, '99'), 'HH24')) or
(specified::time without time zone between to_timestamp(to_char(secondStartHour, '99'), 'HH24') and
to_timestamp(to_char(secondEndHour, '99'), 'HH24'))
)
union
select riderId
from PartTimeWorkingHourIntervals P
where specified between calculate_start_date(P.dayIndex, P.startDate)+P.startHour
and calculate_start_date(P.dayIndex, P.startDate) + P.endHour;
end;
$$
language plpgsql;
create or replace function calculate_start_date(index int, starting date) returns date as
$$
declare
position integer;
begin
select extract(dow from starting::date) into position;
index = (index - position)%7;
return starting + index;
end;
$$
language plpgsql;
time
value with atimestamp
value – a_horse_with_no_name Mar 30 '20 at 18:10