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

score 0 · Answer 1 · answered Dec 15 '14 at 15:10

0

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.

answered Dec 15 '14 at 15:10

Gordon Linoff

1,242,037
58
646
786

I got the error
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<' – brucemccampbell Dec 15 '14 at 15:31
I fount the answer here
http://stackoverflow.com/questions/4714899/mysql-compare-between-time?rq=1 – brucemccampbell Dec 15 '14 at 15:39
Using the Time_to_Sec example – brucemccampbell Dec 15 '14 at 20:04
I imagine the illegal mix of collations is on the `date` part, so you could try `date = date('2014-08-28')`. – Gordon Linoff Dec 16 '14 at 14:38

score -1 · Answer 2 · edited Oct 03 '16 at 11:12

-1

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';

edited Oct 03 '16 at 11:12

Petter Friberg

21,252
9
60
109

answered Oct 03 '16 at 11:06

user6915242

1

Please provide an explanation for your answer. – buhtz Oct 03 '16 at 11:39
1

Welcome to Stack Overflow! Although this code may help to solve the problem, it doesn't explain _why_ and/or _how_ it answers the question. Providing this additional context would significantly improve its long-term educational value. Please [edit] your answer to add explanation, including what limitations and assumptions apply. When the question is vague, this is even more important than usual! – Toby Speight Oct 03 '16 at 16:23

comparison operators between time values mysql

2 Answers2