let serviceDuration = 00:15:00 (15 Minutes Duration)
    let desiredDay      = 2022-12-3

    Table MYSQL: Booking
    ┌────┬────────────────────┬────────────────────┬─────────────┐
    │ ID │   Start Time       │      End Time      │ Client Name │
    ├────┼────────────────────┼────────────────────┼─────────────┤
    │  1 │ 2022-12-3 13:00:00 │ 2022-12-3 14:00:00 │ Connan      │
    │  2 │ 2022-12-3 14:00:00 │ 2022-12-3 14:30:00 │ Dean        │
    │  3 │ 2022-12-3 15:00:00 │ 2022-12-3 15:15:00 │ Holmes      │
    │  4 │ 2022-12-3 15:30:00 │ 2022-12-3 16:30:00 │ Hyuk        │
    │  5 │ 2022-12-3 18:30:00 │ 2022-12-3 19:00:00 │ Ayer        │
    └────┴────────────────────┴────────────────────┴─────────────┘
    Example: 15:15:00 is available to book a service with serviceDuration.

   

I would like to make a QUERY that returns free time slots based on the duration of a specific service serviceDuration of desiredDay and it shows me 3 options in console of times that are available to make the appointment, example:

 Option 1: 12/03/2022 at 15:15
 Option 2: 12/03/2022 at 16:30 
 Option 3: 12/03/2022 at 19:00
 
 Option Input > 

After entering the option, the time will be scheduled. I want to make a simple console application:

 1. Query and obtain available slots for scheduling, based in serviceDuration and desiredDay
 2. Display them in console.
 3. Insert into database (mysql) after selecting the option number in input.

More Informations:

NODE.JS: v16.18.0 (On Computer)
MYSQL: Running using XAMPP (localhost, root, "")

I haven't tried anything yet, I'm looking for direction, ok @MichaelG?

CodePudding user response：

To find available time slots for a specific service and day, you can use the following query:

SELECT 
    startTime, 
    endTime
FROM Booking
WHERE desiredDay BETWEEN startTime AND endTime
    AND (TIMESTAMPDIFF(MINUTE, startTime, endTime) >= serviceDuration)

This query will return the start and end time of all bookings that are within the desired day and have a duration greater than or equal to the service duration.

To display the available time slots in the console, you can use the following code:

const mysql = require('mysql');

// Connection configuration
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'mydb'
});

// Connect to the database
connection.connect();

// Query to find available time slots
const query = `
    SELECT 
        startTime, 
        endTime
    FROM Booking
    WHERE desiredDay BETWEEN startTime AND endTime
        AND (TIMESTAMPDIFF(MINUTE, startTime, endTime) >= serviceDuration)
`;

// Execute the query
connection.query(query, (error, results) => {
    if (error) throw error;

    // Loop through the results and display the available time slots
    results.forEach((result, index) => {
        console.log(`Option ${index   1}: ${result.startTime}`);
    });