W. Jackson W. Jackson - 1 year ago 83
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;

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download