PostgreSQL Question posted in
The official documentation can be found here.

How can I filter available time slots between booked appointments in PostgreSQL?

I am trying to implement an appointment manager for a dentist office.

Thing is I am not sure how to properly implement such function. Right now I have managed to create these two tables that will handle it:

CREATE TABLE Horarios (
    id_horario BIGSERIAL PRIMARY KEY,
    hora TIME UNIQUE NOT NULL
);

CREATE TABLE Consultas (
    id_consulta BIGSERIAL PRIMARY KEY,
    paciente INTEGER REFERENCES Pacientes(id_paciente),
    creado_por INTEGER REFERENCES Administrativo(id_administrativo),
    fecha_designada DATE NOT NULL,
    consulta_completa BOOLEAN DEFAULT false,
    hora_inic INTEGER REFERENCES Horarios(id_horario),
    hora_fin INTEGER REFERENCES Horarios(id_horario),
    dentista INTEGER REFERENCES Dentistas(id_dentista)
);

All hour timestamps are hardcoded values like ("07:00")
So far, its all good I have been using this query to fetch all availables appointments:

SELECT
    h.id_horario,
    h.hora 
FROM 
    Horarios h
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Consultas c
        WHERE
            c.fecha_designada = $1
            AND c.dentista = $2
            AND h.hora >= (SELECT hora FROM Horarios WHERE id_horario = c.hora_inic)
            AND h.hora < (SELECT hora FROM Horarios WHERE id_horario = c.hora_fin)
    )
ORDER BY
    h.hora;

It will check for all, timestamps that are between the start_hour(hora_inic) and the end_hour(hora_fin) of said appointment.

And it works great when you have to create appointments in advance.
But when you have to create lets say:

An appointment from 7:00 to 7:30 and then another from 8:00 to 8:30 it doesn’t let me create an appointment from 7:30 to 8:00.

Now this is obviously a logical error.
I have been trying to change my query and both my backend and frontend. in order to aliviate this problem. But I believe this is just poorly design on my part.

I could use some guidance or advice in how to fix this or how to create an appointment app. Since thats the part that giving me issues right now.

Appreciate the time it took you to read my post. Thank you!

Tags:

2

Answers


  1. 0

    From mentally reviewing the code, nothing seems to be wrong. I would need to run some sample queries to see what the inner query is returning to debug that. Do not do the "SELECT 1", do a "SELECT *" and replace the h.hora value with ‘7:30’.

    But…

    The choice of creating a table to define hours strikes me as odd. You do not need a list of which hours exist in a day. You know they can go from 00:00 to 23:59; you do not need to validate the values.

    More than a master of "hours", the usual way for this is to have a table of "slots" (which might be free or reserved). Each slot has its own fixed starting and ending time. The main downside is that it limits flexibility, if the appointments are of different duration; you can work around this by grouping several slots (either by business logic or creating an 1-N "appointment" class). Also, you need a procedure to generate the slots periodically.

    The benefits are numerous:

    • The query for free slots (which is one of the most frequent you will run) becomes way easier and faster.
    • You have flexibility. If you want to change working hours, you just generate the slots differently. What happens with your current model if you no longer want to schedule appointments at 8:00? You cannot delete the 8:00 record, because you have lots of previous appointments that reference it.
    Login or Signup to reply.
  2. 0

    Tryed to guess the basic data structure and to offer a solution accordingly…

    --      S a m p l e    D a t a : 
CREATE TABLE Horarios (
    id_horario BIGSERIAL PRIMARY KEY,
    hora TIME UNIQUE NOT NULL
);
Insert Into    Horarios (id_horario, hora)
  ( Select   1,    '07:00'::TIME Union All 
    Select   2,    '07:30'::TIME Union All
    Select   3,    '08:00'::TIME Union All
    Select   4,    '08:30'::TIME Union All
    Select   5,    '09:00'::TIME Union All
    Select   6,    '09:30'::TIME Union All
    Select   7,    '10:00'::TIME Union All
    Select   8,    '10:30'::TIME Union All
    Select   9,    '11:00'::TIME Union All
    Select  10,    '11:30'::TIME Union All
    Select  11,    '12:00'::TIME 
);
    id_horario hora
    1 07:00:00
    2 07:30:00
    3 08:00:00
    4 08:30:00
    5 09:00:00
    6 09:30:00
    7 10:00:00
    8 10:30:00
    9 11:00:00
    10 11:30:00
    11 12:00:00 
    --      S a m p l e    D a t a : 
CREATE TABLE Consultas (
    id_consulta BIGSERIAL PRIMARY KEY,
    paciente INTEGER,
    creado_por INTEGER,
    fecha_designada DATE NOT NULL,
    consulta_completa BOOLEAN DEFAULT false,
    hora_inic INTEGER REFERENCES Horarios(id_horario),
    hora_fin INTEGER REFERENCES Horarios(id_horario),
    dentista INTEGER
);
Insert Into Consultas (id_consulta, paciente, creado_por, fecha_designada, 
                       consulta_completa, hora_inic, hora_fin, dentista)
  ( Select     101, 1001, 11, '2024-10-24'::DATE, 
               False, 1, 3, 1 Union All
    Select     102, 1002, 11, '2024-10-24'::DATE, 
               False, 7, 8, 1 Union All
    Select     103, 1003, 11, '2024-10-24'::DATE, 
               False, 3, 4, 2 Union All
    Select     104, 1004, 13, '2024-10-24'::DATE, 
               False, 4, 5, 2 );
    id_consulta paciente creado_por fecha_designada consulta_completa hora_inic hora_fin dentista
    101 1001 11 2024-10-24 f 1 3 1
    102 1002 11 2024-10-24 f 7 8 1
    103 1003 11 2024-10-24 f 3 4 2
    104 1004 13 2024-10-24 f 4 5 2

    … added cte with dentistas to work with their hours individualy …
    The grid cte prepares the data for later aggregations defining the ocupied and available periods as well as mixed groupings

    WITH
  dentistas AS
    ( Select 1 as dentista, 'dentista 1' as nombre Union All
      Select 2 as dentista, 'dentista 2' as nombre  
    ),
  grid AS 
    ( Select        '2024-10-24'::DATE as fecha, -- this is supposed to be a calendar date
                    d.dentista, d.nombre,
                    h.id_horario, h.hora, 
                    c.hora_inic, c.hora_fin, 
                    Case When Coalesce(c.hora_inic, c.hora_fin) Is Null
                         Then 'Disponible'
                    Else 'Ocupado'
                    End as estado, 
               CASE WHEN ( c.hora_inic Is NOT Null And 
                           LAG(c.hora_fin) Over(Partition By d.dentista Order By h.id_horario) Is Null )
                      OR ( c.hora_inic Is Null And 
                           LAG(c.hora_fin) Over(Partition By d.dentista Order By h.id_horario) Is NOT Null )
                    Then h.id_horario
               END as grupo
      From          dentistas d
      Inner Join    Horarios h ON( 1 = 1 )
      Left Join     Consultas c ON( c.dentista = d.dentista And 
                                    h.id_horario Between c.hora_inic And c.hora_fin )
    --  Where       fecha = '2024-10-24'    -- this is supposed to be a calendar date
      Order By      d.dentista, h.id_horario, c.hora_inic
    )

    --      M a i n    S Q L :
SELECT  x.fecha, x.dentista, x.nombre, x.estado, 
        Min(id_horario) as hora_inic_id, Min(hora) as hora_inic, 
        Max(id_horario) as hora_fin_id, Max(hora) as hora_fin
FROM ( Select    fecha, dentista, nombre, id_horario, hora, hora_inic, hora_fin, estado, 
                 Max(Grupo) Over(Partition By dentista Order By id_horario
                            Rows Between Unbounded Preceding And Current Row) as grupo
       From      grid
       Order By  dentista, id_horario
     ) x
GROUP BY x.fecha, x.dentista, x.nombre, x.grupo, x.estado 
ORDER BY x.fecha, x.dentista, x.nombre, Min(id_horario), x.grupo, x.estado

    R e s u l t :

    fecha dentista nombre estado hora_inic_id hora_inic hora_fin_id hora_fin
    2024-10-24 1 dentista 1 Ocupado 1 07:00:00 3 08:00:00
    2024-10-24 1 dentista 1 Disponible 4 08:30:00 6 09:30:00
    2024-10-24 1 dentista 1 Ocupado 7 10:00:00 8 10:30:00
    2024-10-24 1 dentista 1 Disponible 9 11:00:00 11 12:00:00
    2024-10-24 2 dentista 2 Disponible 1 07:00:00 2 07:30:00
    2024-10-24 2 dentista 2 Ocupado 3 08:00:00 5 09:00:00
    2024-10-24 2 dentista 2 Disponible 6 09:30:00 11 12:00:00

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search