Get reservations by time

Question

I have a problem with MySQL, I am trying to get all reservations where the starttime is larger than '16:42' but smaller than '18:42'. I tried this, but when i use '15:42' instead of '16:42' i still got the same result.

SELECT     * 
FROM       `tables` 
INNER JOIN reservation_table 
ON         `tables`.`id` = reservation_table.table_id 
INNER JOIN reservations 
ON         reservations.id = reservation_table.reservation_id 
INNER JOIN customers 
ON         customers.id = reservations.customer_id 
WHERE  `date` = '2016-11-02' 
   AND ( starttime <= '19:42' 
         AND starttime >= '16:42' )

Why not combine date and time into one field: http://stackoverflow.com/questions/5786649/mysql-get-record-count-between-two-date-time — Maximus2012, Nov 02 '16 at 19:23

ScaisEdge · Answer 1 · 2016-11-02T20:02:44.897

1

could be you have some conversion issue .. try using an explicit conversion

SELECT     * 
FROM       `tables` 
INNER JOIN reservation_table 
ON         `tables`.`id` = reservation_table.table_id 
INNER JOIN reservations 
ON         reservations.id = reservation_table.reservation_id 
INNER JOIN customers 
ON         customers.id = reservations.customer_id 
WHERE  `date` = '2016-11-02' 
   AND ( starttime <= str_to_date('19:42', '%H:%i' )
         AND starttime >= str_to_date('16:42' , '%H:%i'))

edited Nov 02 '16 at 20:02

answered Nov 02 '16 at 19:36

ScaisEdge

131,976
10
91
107

what mean doesn't work .. you have error? .. wrong result .? . no result? – ScaisEdge Nov 02 '16 at 19:49
I dont get any result, if i use str_to_date(). Sorry for being unclear. – Jan-Wiebe Nov 02 '16 at 19:53
1

instead without str_to_date you get a result but the result don't change when you change the time string? – ScaisEdge Nov 02 '16 at 19:56
When i use str_to_date, i dont get a result whatever i fill in. When i dont use str_to_date, i get a result, but when i set the time 15:42 instead of 16:42 (in the query.) I still get a result – Jan-Wiebe Nov 02 '16 at 19:59
1

well you shoul update your question adding the schema of the related table .. a proper data sample and the expected result .. – ScaisEdge Nov 02 '16 at 20:00
I have also update the answer because the sintax of the str_to_date format was wrong.. (missing %) – ScaisEdge Nov 02 '16 at 20:03
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Nov 02 '16 at 21:51

score 0 · Answer 2 · answered Nov 03 '16 at 06:46

If starttime and endtime columns are in TIME format then this queries should solve your problem:

Because as mysql documentation say "If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates or part of dates are disallowed." you may need to execute this query first:

SET sql_mode = '';

then the second one (if starttime and endtime columns are in TIME format):

SELECT     *
FROM       `tables` 
INNER JOIN reservation_table 
ON         `tables`.`id` = reservation_table.table_id 
INNER JOIN reservations 
ON         reservations.id = reservation_table.reservation_id 
INNER JOIN customers 
ON         customers.id = reservations.customer_id 
WHERE  `date` = '2016-11-02'
AND ( starttime <= str_to_date('19:42', '%H:%i')
    AND starttime >= str_to_date('16:42' , '%H:%i'))

but if these columns are not in that format do:

SELECT     *
FROM       `tables` 
INNER JOIN reservation_table 
ON         `tables`.`id` = reservation_table.table_id 
INNER JOIN reservations 
ON         reservations.id = reservation_table.reservation_id 
INNER JOIN customers 
ON         customers.id = reservations.customer_id 
WHERE  `date` = '2016-11-02'
AND ( str_to_date(starttime, '%H:%i') <= str_to_date('19:42', '%H:%i')
    AND str_to_date(starttime, '%H:%i') >= str_to_date('16:42' , '%H:%i'))

Get reservations by time

2 Answers2