2

I'm working on booking service.

I have a table where users enter their open hours. These pieces of information allow me to generate slot times for a particular day.

For example, for row one, I know that the user works on Monday morning between 10:00 am and 12:00 am and he proposes a service of 30 minutes.

We have 4 slots of 30 minutes each.

  • 10:00 -> 10:30
  • 10:30 -> 11:00
  • 11:00 -> 11:30
  • 11:30 -> 12:00

enter image description here

When I generate these slot times, I need to relate them to a particular day. For example December 16, Monday.

  • 10:00 -> 10:30
  • 10:30 -> 11:00
  • 11:00 -> 11:30
  • 11:30 -> 12:00

But I don't know how to do that. In my database, I know that the particular user works on Monday, but it's mean nothing for SQL. How can I generate that on a particular Monday?

For the moment, I don't have a lot of things to show...

I created this table, it represents the working hours of a User.

For example.

  • Monday 10:00am to 12:00am
  • Monday 3:00pm to 6:00pm
CREATE TABLE allow_day_and_time (
    id BIGINT NOT NULL
    workingday varchar(50) NOT NULL,
    start_time time NOT NULL,
    end_time time NOT NULL,
);

I had an idea, but I'm not sure if its the right way to do that...

  1. Generate a series of days between today and a random end day.
  2. Return the timestamps of all allowed days filled on allow_day_and_time table.
CREATE OR REPLACE FUNCTION fetchWorkingDays()
 RETURNS TABLE(id bigint, workingday character varying, end_time time without time zone, start_time time without time zone)
 LANGUAGE plpgsql
AS $function$
DECLARE
        day_ VARCHAR;
BEGIN
    FOR day_ IN
    SELECT
        TO_CHAR(date_trunc('day', DD)::DATE, 'day')
    FROM
        generate_series(NOW()::timestamp, '2019-12-31'::timestamp, '1 day'::interval) DD LOOP
        RETURN QUERY SELECT * FROM allow_day_and_time WHERE allow_day_and_time.workingday = '_day';     
    END LOOP;
END;
$function$

Maybe, I was not clear in my explanation.

The main idea of this is to generate slot times by taking to account all constraints.

I don't want to record the availabilities in DB but record booking slots.

What're these constraints?

The user has working hours stocked in allow_days_and_times table (Which means that the user can work on Monday to 10:00 am to 3:00 pm for example).

Here the allow_days_and_times table

CREATE TABLE allow_day_and_time (
period_id uuid DEFAULT uuid_generate_v4(),
day VACHAR(50) NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL
)

The user has blocked periods stocked in block_period table, like Christmas holidays for example.

Here the block_period table

CREATE TABLE block_period (
period_id uuid DEFAULT uuid_generate_v4(),
start_period TIMESTAMP NOT NULL,
end_period TIMESTAMP NOT NULL
)

Finally, the user has theses booked times stocked in booked_slots

Here the booked_slots table

CREATE TABLE booked_slots (
booking_id uuid DEFAULT uuid_generate_v4(),
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL
)

I've simplified as much as possible the table.

Okay, now, let's say I have an API in charge to find all free slots for a particular user. GET /api/user/xxx/availabilities

How can I generate all the availabilities for this user by taking to account all these constraints?

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.