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

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