How to get the interval between two time in query?

Question

Can you please help me get the interval of these two column |clockin|clockout| in my database using mysql. I tried the timediff below but the problem is, example: i clock in 23:00:00 this evening of 09/22/16 and i clocked-out 01:00:00 of morning of 09/23/16, i get a wrong result which is -22:00:00 hrs? The result i need to get is the 02:00:00 hrs. Please help me.

select timeDiff('clockout', 'clockin') as interval;

I really need help on this please to those who have a wide knowledge in programming. Please help me. — W. Jackson, Sep 22 '16 at 02:14

Gordon Linoff · Accepted Answer · 2016-09-22T02:42:57.027

0

I think your problem is simply that you need to add 24 hours under some circumstances:

select (case when clockout > clockin
             then timeDiff(clockout, clockin) 
             else addtime(timeDiff(clockout, clockin), '24:00:00')
        end) as diff

edited Sep 22 '16 at 02:42

answered Sep 22 '16 at 02:19

Gordon Linoff

1,242,037
58
646
786

Thankyou Gordon Linoff, i'll try your answer, hope it will work. – W. Jackson Sep 22 '16 at 02:22
I still got the wrong result the thing is mine returns negative your's is not which returns 22:00:00 hrs. What result i am trying to get is the 02:00:00 hrs. – W. Jackson Sep 22 '16 at 02:40
@W.Jackson . . . I'm confused. In the question you say that you get -22 hours. When you add 24 hours to that, you should be getting 2 hours. – Gordon Linoff Sep 22 '16 at 02:44
Thanks W.Jackson it's working now, your code is working my fault i ddin't check my data that i put there..Thank you verry much. – W. Jackson Sep 22 '16 at 03:33
Im new to Stack overflow is there anyway to like or vote you for helping me? – W. Jackson Sep 22 '16 at 03:36
ahh ok..tnx again Gordon Linoff – W. Jackson Sep 23 '16 at 02:39

How to get the interval between two time in query?

1 Answers1