Database function that takes as input date and time

Question

Looking for a bit of guidance here on an SQL function.

I have this table here:

CREATE TABLE appointments
(
    patient varchar(20) NOT NULL,
    doctor varchar(20) NOT NULL,
    apt_date date NOT NULL,
    apt_start time NOT NULL,
    apt_end time NOT NULL,

    CONSTRAINT pk_appointments PRIMARY KEY (patient, apt_date)
);

I'm looking for a function that takes as input a date and time and returns the number of appointments active at a given date and time.

Any help appreciated.

Answer 1

Please check below PostgreSQL function activeAppointments() which takes input date and time and returns the number of appointments active at a given date and time.

CREATE OR REPLACE FUNCTION public.activeappointments(
    ondate date,
    fromtime time without time zone,
    totime time without time zone)
    RETURNS integer
    LANGUAGE plpgsql
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
    total integer;
BEGIN
   SELECT count(*) into total FROM appointments 
   where apt_date = ondate and apt_start >= fromTime and apt_end <= toTime;
   RETURN total;
END;
$BODY$;

I have taken above mentioned "appointments" table and inserted below sample data to it.

insert into appointments values ('Patient001', 'Doc001', '01-JAN-2021','120000','130000');
insert into appointments values ('Patient002', 'Doc001', '01-JAN-2021','133000','143000');
insert into appointments values ('Patient003', 'Doc001', '01-JAN-2021','150000','160000');

• Total records inserted 3.

select count(1) from appointments; 

• To run the function, please use the below script.

SELECT activeAppointments('01-JAN-2021', '120000', '150000'); --2