Mysql – comparing two time values with 2 column respectively after converting in 24 hour format

MySQL

Hi I have two varchar column with time values store in 12 hour format. I want to get the output like rows that which don't fall in 10:00 am to 12:00 pm

here

id  |time1     | time2
____|__________|__________
1   |01:00 pm  | 10:00 pm
2   |11:00 am  | 01:00 pm
3   |09:00 am  | 11:00 am
4   |05:00 pm  | 11:59 pm

output will be

1   |01:00 pm  | 10:00 pm
4   |05:00 pm  | 11:59 pm

here is the link of sqlfiddle to test
http://sqlfiddle.com/#!9/b6bf48/2

Best Answer

Based on the example output I'm assuming the requirement is to display rows where the time1-time2 range falls completely outside of the desired 10:00am-12:00pm range.

Assumption:

  • time2 >= time1

This is going to be easier if we convert the strings to times and let the database do the time comparisons for us.

In MySQL we can convert the given strings to (date)times via the following construct:

  • str_to_date(<string_value>,'%h:%i %p')

The rows we're looking for should match one of the following tests:

  • time1 > 12:00 pm
  • time2 < 10:00 am

One possible solution:

select time1,
       time2
from   splash
where  str_to_date(time1,'%h:%i %p') > str_to_date('12:00 pm','%h:%i %p')
or     str_to_date(time2,'%h:%i %p') < str_to_date('10:00 am','%h:%i %p')
order by 1,2;

time1      time2
========   ========
01:00 pm   10:00 pm
05:00 pm   11:59 pm

Was having some timeout issues with the OPs fiddle so here's a rextester fiddle for the above solution.