Mysql query for getting restaurant remaining seats

Question

I have problem to write mysql select query in which I can get number of free seat in time slot.

Let me define my problem user mysql query:

Suppose I have one table restaurant_booking.

If there are total 25 seats in restaurant and one user want to 15 seat between 2:00pm - 4:00pm how to check in booking table till this time how many seats are available?

If i see this booking table there are 20 seats already booked till this time slot, then how should i get only 5 seats are available?

If anyone have any idea, please let me know. Thanks.

Answer 1

Try

select
    sum(booked_seat) as seatsAva
from
    restaurant_booking
where 
    STR_TO_DATE(end_booking_time,'%r') < DATE_FORMAT(NOW(), '%r')

and after that you can minus seatsAva from 25, above query will give you the result of currently unavailable seats count

Answer 2

The logic looks like:

SELECT (25-SUM(booked_seat)) FROM rest_booking 
WHERE end_booking_time>2 
AND
start_booking_time<4
--2 & 4 are the new customer's start&end booking time

But I agree with @Jesbus, dates should be stored as Timestamps.