CODEWITHSUNDEEP
sqlpostgresqldatabase-design
Stephen A. Lizcano
Stephen A. Lizcano

Reputation: 442

Appointment scheduling in Postgres: querying available times for appointments?

I have a simple schedule table as follows:

CREATE TABLE provider_schedule(
    id SERIAL PRIMARY KEY,
    provider_id uuid,

    date_from timestamptz NOT null,
    date_to timestamptz NOT null,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    FOREIGN KEY (provider_id) REFERENCES app.providers (cognito_id) ON UPDATE CASCADE
);

And I insert some availability times here ('2020-05-12T19:00:00+00:00' to '2020-05-12T23:00:00+00:00'):

insert into app.provider_schedule (provider_id, date_from, date_to) 
values 
('ffc8631f-22d6-4519-b738-793a74cab731', '2020-05-12T19:00:00+00:00','2020-05-12T23:00:00+00:00');

But when querying it as such, it returns no results, when the time is clearly between the schedule?

select providers.first_name, providers.last_name, provider_schedule.date_from, provider_schedule.date_to 
FROM app.provider_schedule, app.providers
where provider_schedule.provider_id = providers.cognito_id
  and provider_schedule.date_from >= '2020-05-12T21:15:00+00:00'
  and provider_schedule.date_to <= '2020-05-12T21:30:00+00:00';

What am I doing wrong here that is not Provider whose schedule availability clearly falls between the query window time?

Upvotes: 0

Views: 868

Answers (1)

user330315
user330315

Reputation:

Your condition on the range is wrong.

The column date_from from your example contains the hour 19:00. But your condition requires date_from >= 21:15 - obviously that isn't matching as 19:00 is not greater than 21:15

You need to swap the edges:

SELECT pr.first_name, pr.last_name, ps.date_from, ps.date_to 
FROM app.provider_schedule ps
  JOIN app.providers pr ON ps.provider_id = pr.cognito_id
WHERE '2020-05-12T21:15:00+00:00' >= ps.date_from
  and '2020-05-12T21:30:00+00:00' <= ps.date_to;

Alternatively this can be done using a timestamp range:

SELECT pr.first_name, pr.last_name, ps.date_from, ps.date_to 
FROM app.provider_schedule ps
  JOIN app.providers pr ON ps.provider_id = pr.cognito_id
WHERE tstzrange(ps.date_from, ps.date_to) @> tstzrange('2020-05-12T21:15:00+00:00', '2020-05-12T21:30:00+00:00');

The @> operator means "does the left range contain the right range"

Upvotes: 2

Related Questions