comparison operators between time values mysql

Question

I am trying to return all the records on a certain date and less than an exact time
my SQL looks like this

• WHERE date = '2014-08-28'
• and UNIX_TIMESTAMP(time) < '14:48:22'
  This returns all the time records and doesn't cutoff the time at the specified point
  
  
• WHERE date = '2014-08-28'
• and time < '14:48:22'
  This query cuts off the time at '14:28:22' but also cuts off all time before '10:00:00'

I have also tried the cast() but no luck

Answer 1

You might try this:

WHERE date = '2014-08-28' and time < time('14:48:22')

It looks like the comparison without time() is somehow being done with numbers rather than times.

Answer 2

SELECT COUNT(0) FROM oc_marchenthoursofoperations 
WHERE dayes = DAYNAME(NOW()) AND TIME(CURRENT_TIME()) BETWEEN TIME( CONVERT(`fromtime`,TIME)) 
AND TIME(CONVERT(`totime`,TIME))  AND `marchentid`='MTN-MAR-00052';