I have date and time stored as 2 columns in MySQL table using type varchar. Time format is 12 hour format (11:59:59 am, 12:59:59 pm, 01:59:59 pm, etc.) as in attached files.
I’d like to query to get data between 11:13:00 am and 12:13:59 pm. The query I used as
SELECT * FROM `table` WHERE date = '10-09-2021' and ((time BETWEEN '11:13:00 am' and '11:59:59 am') or (time BETWEEN '12:00:00 pm' and '12:13:59 pm'))
It give me all data including pm data like 11:20:23 pm in attached files. Please advise how to query. Alternative way is I’d like to get data within 1 hour range. Thank you.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
To query on pure varchar type, it needs to filter am/pm
SELECT * FROM `table` WHERE `date` = '10-09-2021' AND ((`time` LIKE '% am' AND `time` BETWEEN '11:13:00 am' and '11:59:59 am') OR (`time` LIKE '% pm' AND `time` BETWEEN '12:00:00 pm' and '12:13:59 pm'))
to query one hour range,
`time` LIKE '11:% am'
Other option is to convert the varchar time to a date using STR_TO_DATE(), and use standard between … and … , side effect is that you probably cannot use the table index on the column.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0