Find how many slots I can use within the given period of time

user_8488291 0
Viewed 92

I am working on the booking / reservation project and faced some interesting issue.

The point of the project is to determine if there is any time slot available for reservation within the given period of time (12:00 - 15:00).

Also, it supports the N amount of rooms / tables allowed to be reserved. (for example, maximum it could be reserved up to 30 rooms)

Currently, data is stored in the mysql db, with the structure:

id, start_time, end_time and some another payload

Firstly, I was sure that I can do a simple sql select do determine overlaps and count the amount of rows returned

sql-query looks like this:

SELECT * FROM reservations WHERE ((start_time < '${end_time}' AND start_time >= '${start_time}') 
        OR (start_time <= '${start_time}' AND end_time >= '${end_time}') OR (end_time > '${start_time}')

${start_time} - time recieved from the front-end
start_time - column name in the database table.

But eventually I end up with the huge bug. Timeline is horizontal, each rectangle represents one of the created reservations. Empty slot means that there is an empty table / room that could be reserved.

But using this select in order to check available table for time range from 16:00 until 19:00 - query returns more than 50 rows and the if > N_amount replies that there is no available table / room for that time available (but that's obviously not true).

example row:id=1, start_time=2022-08-06 12:00:00, end_time=2022-08-06 15:00:00

Every time slot is rounded to **:30 or **:00 minutes

Any ideas on how to change the query / logic in order to achieve the desired behaviour?

front-end table

mysql javascript
2 Answers

When i understand you right, you are trying to get all reservations between a start time and a end time. (start_time < '${end_time}' AND start_time >= '${start_time}') This query gives you every reservation with a start time before the end oft the timeslot and a start time after the start of the timeslot

(start_time <= '${start_time}' AND end_time >= '${end_time}') With that query you are recieving every reservation with a start_time before the timeslot and with a end time after the timeslot

(end_time > '${start_time}') With that query you are recieving every reservation whith a end time after the start time requested from the frontend, which is giving you every reservation with a end time that is after the start time requested. I think that this is the main problem.

Now firstly please take a look at this page https://www.stackhawk.com/blog/node-js-sql-injection-guide-examples-and-prevention/ which is giving you important informations about SQL-Injection. Your code has a major security problem! In Node.JS a fix for that can look like this:

connection.query('SELECT * FROM sheet where id = ?', [id], (err, rows) => {
//code
  });

To get better results i would recommend to fetch all reservations that only have a start time in that timeslot and then putting the whole reservation into your table. SELECT * FROM reservations WHERE ((reservations.start_time >= *start_time* AND reservations.start_time < *end_time*) OR (reservations.end_time > *start_time* AND reservations.end_time <= *end_time*))

With that query you are recieving all reservations that have a start time in that range or a end time in that range which should give you good results. Please remember to replace my ** fillings with ? as shown in the example before

user_197057810

You did not give enough information.

  • Keeping it simple each row in the SQL table represents the room/table and date being reserved.

  • No one starts on one day and holds it until the next day. For example start time = 23:00 for an hour and a half, crossing midnight into the next day.

Rather than ask the user for date, start time, and end time, I would ask for the date(s) of service, then give them a matrix of check boxes that represent the slots available for the user to check.

I will assume the SQL table has a row for each room and date where the row columns are:
date,room,slot0, ... slot47 (48 half hour slots) assuming 24 hour availability

I would create an object for conversion of time slot number (0-47) to the time slot column name (0-47): 

const slots = ['slot0','slot1',...'slot47'];

You calculate the time slot for the first half hour.
Then calc the number of slots needed.
From that you build the query. For a 15:00 start time and 3 slots needed.
15:00 translates to slot29 and 15:30 translates to slot30.

You could use another object to translate the hour to a time slot:<

const hour2slot = {0:[0],1:[2],2:[4]... 23:[46]}

Then translate the minutes :00 and :30 to one or zero.

const minutes = {0:[0], 30:[1]}

So the first time slot:

timeslot1 = hour2slot[hour] + minutetoslot[minutes]

Then build the query with a for loop.

var where = 'WHERE `DATE` = date AND' + slots[timeslot1] + "= ''"  
for slot=timeslot1, slot < number_of_slots,slot++){
  var where = where  + ' AND ' +  slots[slot] + "=''"
} 
var sql = 'SELECT `room` FROM `table` ' + where + ' LIMIT 1'

I did not check my math or code, but you should get the idea.
The result would be:

SELECT `room` FROM `table` WHERE `slot29` = '' AND `slot30`='' AND `slot31` = '' LIMIT 1

But this would be so much simpler to create a table with the available time slots to be selected by the user.

BTW, I never use SELECT * FROM

user_38136050
Related