mysql - between two times in one day

Question

MySQL rows:

id  work_time_from  work_time_to
1   12:30           23:00
2   14:00           18:00

input data:

$curTime = 13:15

Problem:

I have to fetch all rows corresponding condition work_time_from < $curTime < work_time_to

My request is:

SELECT * FROM companies WHERE work_time_from < "13:15" AND work_time_to > "13:15"

It doesn't work i think because of work_time_from, work_time_to varchar type.

Why doesn't it work with `varchar`? Make sure you have leading `0` on times before `10:00`. — Barmar, Feb 04 '16 at 09:49
It works correctly here: http://www.sqlfiddle.com/#!9/84045d/1 — Barmar, Feb 04 '16 at 09:50

Matt · Accepted Answer · 2016-02-04T11:33:59.697

2

Use STR_TO_DATE to convert your fields and then BETWEEN to compare.

SELECT *
FROM yourtable
WHERE STR_TO_DATE(CURTIME(),'%h:%i') BETWEEN STR_TO_DATE(work_time_from,'%h:%i') AND STR_TO_DATE(work_time_to,'%h:%i')

edited Feb 04 '16 at 11:33

answered Feb 04 '16 at 09:21

Matt

thank you, some correction: `SELECT * FROM yourtable WHERE STR_TO_DATE($curTime, '%H;%i') BETWEEN STR_TO_DATE(work_time_from ,'%H:%i') AND STR_TO_DATE(work_time_to,'%H:%i')` – Michael Feb 04 '16 at 11:29
@Michael i have udpated my answer – Matt Feb 04 '16 at 11:33

1 Answers1