Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Unanswered: Finding Gaps between Datetime Ranges

    I'm working on an application that will help doctors schedule their own appointments, and I'm having trouble finding open slots in their schedules.

    The database has a handful of tables that look like this:

    doctors
    id
    ...

    hours (the open and closing times for the doctor's office)
    id
    doctor_id (fk)
    open_at (datetime)
    closed_at (datetime)

    patients
    id
    ...

    appointments
    doctor_id (fk)
    patient_id (fk)
    starts_at (datetime)
    ends_at (datetime)

    ...and now I'm stuck trying to find open gaps for new appointments.

    This is the scenario I'm considering:

    1. For a given date, appointment length (in minutes), and doctor
    2. find available appointment times* that
    2a. do not overlap another appointment for that doctor and
    2b. do overlap the doctor's hours. (The doctor has to be open to take the appointment, after all!)

    With as many scheduling apps that are out there, this has to be a well-solved problem, right? The trouble is that I don't think I know what to search for to research the question.

    I can use something like Ruby to loop over the various records and find the gaps, but I'd like to query the database directly for the gaps, if possible. Brute-force isn't very efficient ;-)

    Can you give me some pointers on how to think about this problem?

    Thanks very much in advance,

    Jacob

  2. #2
    Join Date
    Jul 2015
    Posts
    7
    hi jacob,

    i have tried something. hope it will work for you.

    so i created sample datas appropriate to your tables.

    doctors, with id and name;
    hours, working hours for each doctor;
    patients, with id and name;
    appointments, doctors have appointment in some hours of the day

    1. For a given date, appointment length (in minutes), and doctor

    select d.name,
    EXTRACT(EPOCH FROM a.ends_at - a.starts_at) / 60 as minutes,
    a.starts_at::date
    from test.appointments a
    inner join test.patients p on p.id = a.patient_id
    inner join test.doctors d on d.id = a.doctor_id
    where
    a.starts_at::date = '17.06.2015'
    order by a.starts_at asc;

    "name" "minutes" "starts_at"
    "john easy" "30" "17.06.2015"
    "john easy" "15" "17.06.2015"
    "john easy" "10" "17.06.2015"
    "samuel hard" "12" "17.06.2015"
    "john easy" "25" "17.06.2015"
    "john easy" "5" "17.06.2015"


    2. find available appointment times* that
    2a. do not overlap another appointment for that doctor and
    2b. do overlap the doctor's hours. (The doctor has to be open to take the appointment, after all!)

    i have wrote stored procedure which gives back gap times;

    for example appoinments with patients:
    "name" "starts_at" "ends_at"
    "john easy" "17.06.2015 07:00:00" "17.06.2015 07:30:00"
    "john easy" "17.06.2015 10:00:00" "17.06.2015 10:15:00"
    "john easy" "17.06.2015 11:30:00" "17.06.2015 11:40:00"
    "samuel hard" "17.06.2015 16:30:00" "17.06.2015 16:42:00"
    "john easy" "17.06.2015 17:30:00" "17.06.2015 17:55:00"
    "john easy" "17.06.2015 17:55:00" "17.06.2015 18:00:00"

    so the doctors working hours:

    "name" "open_at" "closed_at"
    "john easy" "17.06.2015 07:00:00" "17.06.2015 18:00:00"
    "samuel hard" "17.06.2015 07:00:00" "17.06.2015 18:00:00"

    so the solution should be like that (gap times available for appoinment)

    "doctor_name" "starts_time" "ends_time"
    "john easy" "17.06.2015 07:30:00" "17.06.2015 10:00:00"
    "john easy" "17.06.2015 10:15:00" "17.06.2015 11:30:00"
    "john easy" "17.06.2015 11:40:00" "17.06.2015 17:30:00"
    "samuel hard" "17.06.2015 07:00:00" "17.06.2015 16:30:00"
    "samuel hard" "17.06.2015 16:42:00" "17.06.2015 18:00:00"


    DDL:

    CREATE OR REPLACE FUNCTION test.function3 (
    out doctor_name text,
    out starts_time timestamp,
    out ends_time timestamp,
    given_date date
    )
    RETURNS SETOF record AS
    $body$
    DECLARE
    appointments record;
    doctors_hours record;
    starts_date_c TIMESTAMP;
    BEGIN

    --available the doctor's hours
    for doctors_hours IN
    select d.name,
    d.id,
    h.open_at,
    h.closed_at
    from test.doctors d
    inner join test.hours h on h.doctor_id = d.id
    where h.open_at::date = given_date

    LOOP

    --init start_time
    starts_date_c =doctors_hours.open_at;

    --appointments with doctors
    for appointments in
    select d.id,
    a.starts_at,
    a.ends_at
    from test.appointments a
    inner join test.patients p on p.id = a.patient_id
    inner join test.doctors d on d.id = a.doctor_id
    where a.starts_at::date = given_date and
    d.id = doctors_hours.id
    order by a.starts_at asc

    loop

    if(starts_date_c=appointments.starts_at) then

    --set start time for next appointment
    starts_date_c = appointments.ends_at;

    elsIF (appointments.starts_at > starts_date_c) then

    doctor_name = doctors_hours.name;
    starts_time = starts_date_c;
    ends_time = appointments.starts_at;

    --set start time for next appointment
    starts_date_c = appointments.ends_at;

    return next;

    end if;

    end loop;

    --closing appointment gap table
    if(appointments.ends_at<>doctors_hours.closed_at) then

    doctor_name = doctors_hours.name;
    starts_time = starts_date_c;
    ends_time = doctors_hours.closed_at;

    return next;
    end if;

    end loop;

    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 100 ROWS 1000;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •