How to Search Records In MySQL Using StartTime(varchar) And EndTime(varchar)?

Question

Here I have some records, I want to search for records from 8:30 AM to 9:30 AM.

I tried these query but not working:

select * from table_name where '8:30 AM' BETWEEN start_time and end_time OR '9:30 AM' BETWEEN start_time and end_time

select *
from time_test
where STR_TO_DATE(start_time, '%h:%i %p') BETWEEN STR_TO_DATE('08:30 AM', '%h:%i %p') AND STR_TO_DATE('09:30 AM', '%h:%i %p')
    OR STR_TO_DATE(end_time, '%h:%i %p') BETWEEN STR_TO_DATE('08:30 AM', '%h:%i %p') AND STR_TO_DATE('09:30 AM', '%h:%i %p')

Here is table sql :

DROP TABLE IF EXISTS `time_test`;
CREATE TABLE IF NOT EXISTS `time_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `start_time` varchar(20) NOT NULL,
  `end_time` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

INSERT INTO `time_test` (`id`, `name`, `start_time`, `end_time`) VALUES
(1, 'R1', '8:00 AM', '9:00 AM'),
(2, 'R2', '9:00 AM', '10:00 AM'),
(3, 'R1', '8:00', '9:00'),
(4, 'R2', '9:00', '10:00'),
(5, 'R2', '1:00 PM ', '2:00 PM'),
(6, 'R2', '13:00 ', '14:00'),
(7, 'R1', '8:00 PM', '9:00 PM'),
(8, 'R1', '8:30 AM', '9:30 AM');

Answer 1

You could use the DATE() function. In my example using DATETIME. Can try with your database records.

SELECT `yourdatabase`
  FROM `yourtable`
 WHERE DATETIME(`yourcolumn`) = '2019-06-17 08:30'

However, for better performance you could use..

  WHERE `yourcolumn` 
BETWEEN '2019-06-17 08:30:00'
    AND '2019-06-17 09:30:00'

You can check HERE for details or read THIS.

Answer 2

You can convert to minutes and add where conditions: View example :

SELECT *
FROM my_table 
WHERE 
    ( 
        HOUR(start_time ) * 60 + MINUTE(start_time) >= HOUR('8:30 AM' ) * 60 + MINUTE('8:30 AM')
        AND 
        HOUR(start_time ) * 60 + MINUTE(start_time) <= HOUR('9:30 AM' ) * 60 + MINUTE('9:30 AM')
    )
    OR 
    ( 
        HOUR(end_time ) * 60 + MINUTE(end_time) >= HOUR('8:30 AM' ) * 60 + MINUTE('8:30 AM')
        AND 
        HOUR(end_time) * 60 + MINUTE(end_time) <= HOUR('9:30 AM' ) * 60 + MINUTE('9:30 AM')
    )

Answer 3

since you're not storing the data as DATETIME format, to select a records ranged limited to only within 8:30 to 9:30, you could use STR_TO_DATE :

select *
from table_name
where (STR_TO_DATE(start_time, '%h:%i %p') BETWEEN STR_TO_DATE('08:30 AM', '%h:%i %p') AND STR_TO_DATE('09:30 AM', '%h:%i %p'))
    AND (STR_TO_DATE(end_time, '%h:%i %p') BETWEEN STR_TO_DATE('08:30 AM', '%h:%i %p') AND STR_TO_DATE('09:30 AM', '%h:%i %p'))

To also select a records ranged outside the 8:30 and 9:30 range :

select *
from table_name
where (STR_TO_DATE(start_time, '%h:%i %p') BETWEEN STR_TO_DATE('08:30 AM', '%h:%i %p') AND STR_TO_DATE('09:30 AM', '%h:%i %p'))
    OR (STR_TO_DATE(end_time, '%h:%i %p') BETWEEN STR_TO_DATE('08:30 AM', '%h:%i %p') AND STR_TO_DATE('09:30 AM', '%h:%i %p'))