1

I have table structure in mysql,

table_id  no_people  booking_date  bookingend_time      bookingstart_time
      14          2  2014-10-31    2014-10-31 13:30:00  2014-10-31 11:00:00
       5          4  2014-10-31    2014-10-31 16:30:00  2014-10-31 14:30:00
       6          2  2014-10-31    2014-10-31 17:00:00  2014-10-31 16:00:00
       2          4  2014-11-06    2014-11-06 12:30:00  2014-11-06 10:00:00
       2          4  2014-10-31    2014-10-31 16:00:00  2014-10-31 14:00:00
       3          4  2014-11-01    2014-11-01 09:00:00  2014-11-01 07:30:00
       6          2  2014-11-01    2014-11-01 10:00:00  2014-11-01 07:30:00
       2          4  2014-11-03    2014-11-03 10:30:00  2014-11-03 08:30:00
       5          4  2014-11-04    2014-11-04 10:30:00  2014-11-04 08:30:00
       3          4  2014-11-05    2014-11-05 09:30:00  2014-11-05 07:30:00
      14          2  2014-11-05    2014-11-05 09:30:00  2014-11-05 07:30:00

I want to retrieve table_id data with 30 minutes of interval between start and end time.

Ex: if i give booking start time 10:30 and end time 12:30 i should get 14 as row.. Similarly it should check all rows and return between two times ..

My query so far

SELECT `table_id` FROM `booking` WHERE bookingstart_time>='2014-10-31 10:30:00' AND bookingend_time<='2014-10-31 11:30:00'
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Arockiaraj
  • 353
  • 2
  • 4
  • 21
  • Could it be that you want to get rows that were "busy" any time during, or within 30 minutes of the specified interval? In other words, if the input search interval is "10:00-11:00", then you want to pull rows with start/end times "09:00-12:00", "11:01-13:00", "09:00-09:59", but not "09:00-09:29"? – RandomSeed Nov 21 '14 at 00:20
  • yes. how can i do this – Arockiaraj Nov 24 '14 at 07:04
  • You may want to document these cases in your question. It is better to keep all relevant information inside the question body. – RandomSeed Nov 24 '14 at 23:23

2 Answers2

1

Step 1: expand the input time frame by 30 minutes before and 30 minutes after. DATE_ADD() and DATE_SUB() can do that:

DATE_SUB(_input_start_date_here_, INTERVAL 30 MINUTE)

Step 2: rethink your problem in terms of start and end times. Here are the possible cases:

  • if the booking started during the (expanded) period, then you want this booking in your result
  • or if the booking started before the period, then you want this booking unless it also ended before the period
  • on the other hand, if the booking started after the period, then you do not want this booking

The first situation above could be expressed like this:

WHERE bookingstart_time >= DATE_SUB(_input_start_date_here_, INTERVAL 30 MINUTE)
AND bookingstart_time <= DATE_ADD(_input_end_date_here_, INTERVAL 30 MINUTE)

The second condition is left as an exercise. You can also rewrite the above with a more elegant BETWEEN operator.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
0

SELECT restaurant_table FROM rest_restaurantbooking WHERE TIMESTAMPDIFF(SECOND, bookingstart_time, bookingend_time) > 1800.

FOR REFERENCE: HERE

Community
  • 1
  • 1
Sachin Ananth Navale
  • 1
  • It showing all tables.I need to retrieve tables where occurs between the given booking start and end time.This Query is query is not relevant for me. – Arockiaraj Nov 21 '14 at 05:52