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
        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'))

        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;
language plpgsql;

create or replace function calculate_start_date(index int, starting date) returns date as
        position integer;
        select extract(dow from starting::date) into position;
        index = (index - position)%7;
        return starting + index;
language plpgsql;
  • I suggest you create an minimal reproducible example instead of posting your complete function code; most of it is irrelevant to your question and makes answering harder. – mustaccio Mar 30 '20 at 17:42
  • 2
    What's unclear about the error message? You can't compare a time value with a timestamp value – a_horse_with_no_name Mar 30 '20 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.