W. Jackson W. Jackson - 29 days ago 5x
MySQL Question

How to get the interval between two time in query?

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