I have a time string as 8:00AM, 9:00AM etc. stored in mysql table. I want to check time range, if time 11:00AM comes in between开发者_StackOverflow社区 time 9:00AM - 6:00PM? How to find it out in mysql.

Time is stored as below

day         depart_time     return_time
Monday      9:00am          5:00pm
Wednesday   9:30am          4:30pm
Tuesday     9:00am          3:00pm

You could use STR_TO_DATE to convert the string data types into time. In the example below, all rows where "11:00am" is between the departure time and return time will be returned.

SELECT `depart_time`,
       `return_time`
FROM   `table`
WHERE  STR_TO_DATE('11:00am', '%l:%i%p') BETWEEN STR_TO_DATE(`depart_time`, '%l:%i%p') AND STR_TO_DATE(`return_time`, '%l:%i%p')

The STR_TO_DATE function parses the string according to a specified format. In this case, it's:

• %l: The hour (1-12)
• %i: The minutes (00-59)
• %p: AM/PM

SELECT depart_time, return_time FROM SCHEDULE
WHERE CASE
WHEN STR_TO_DATE(depart_time, '%l:%i%p') < STR_TO_DATE(return_time, '%l:%i%p') THEN
    STR_TO_DATE(depart_time, '%l:%i%p') <= @check_time AND @check_time < STR_TO_DATE(return_time, '%l:%i%p')
ELSE
    NOT(STR_TO_DATE(return_time, '%l:%i%p') <= @check_time AND @check_time < STR_TO_DATE(depart_time, '%l:%i%p'))
END

Substitute @check_time with the time you want to check. This query will work across times that span over two days.

Sample data:

depart_time return_time
9:00am      5:00pm
11:00pm     1:00am

Sample output:

#SET @check_time = STR_TO_DATE('8:59am', '%l:%i%p');
(no result)

#SET @check_time = STR_TO_DATE('9:00am', '%l:%i%p');
9:00am      5:00pm

#SET @check_time = STR_TO_DATE('11:00am', '%l:%i%p');
9:00am      5:00pm

#SET @check_time = STR_TO_DATE('4:59pm', '%l:%i%p');
9:00am      5:00pm

#SET @check_time = STR_TO_DATE('5:00pm', '%l:%i%p');
(no result)

#SET @check_time = STR_TO_DATE('10:59pm', '%l:%i%p');
(no result)

#SET @check_time = STR_TO_DATE('11:00pm', '%l:%i%p');
11:00pm     1:00am

#SET @check_time = STR_TO_DATE('12:00am', '%l:%i%p');
11:00pm     1:00am

#SET @check_time = STR_TO_DATE('12:59am', '%l:%i%p');
11:00pm     1:00am

#SET @check_time = STR_TO_DATE('1:00am', '%l:%i%p');
(no result)

Try this.

select TIMEDIFF ('2006-10-31 11:50:31' , '2006-10-31 11:50:01')

you need to give 24 hr format.,

SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');