CODEWITHSUNDEEP
mysqlsqldate
Jack Smother
Jack Smother

Reputation: 207

Comparing TIMEDIFF in MySQL

I have these following data:

ID   TIMEIN                 TIMEOUT
1    2016-08-24 20:44:00    2016-10-22 19:35:36
2    2016-08-24 20:58:00    2016-08-24 20:59:09

and the following query that I want to use to find all the entries where the timediff is lesser than 50 hours

SELECT TimeDiff(TimeOut,TimeIn) 
from table where TimeDiff(TimeOut,TimeIn) < '50:00:00'

The second row should be returned because it's lesser than 50 hours, but the first row, which the timediff is more than 50 hours keep returning as well.

02:04:57
15:10:49
125:00:25

It returns all sort of value, including those larger than 50

What query should I be using? Thank you

Upvotes: 5

Views: 4309

Answers (2)

toonice
toonice

Reputation: 2236

Please use the following:

SELECT id,
       timeIn,
       timeOut,
       TIMEDIFF( timeOut,
                 timeIn ) AS timeDifference
FROM table
WHERE TIMESTAMPDIFF( SECOND,
                     timeOut,
                     timeIn ) > 180000;

This statement will output the fields for each record where the difference between the two timedates is one second or more over 50 hours (or 180000 seconds).

Upvotes: 6

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Try getting the difference in hours and then comparing.

SELECT TimeDiff(TimeOut,TimeIn) 
from table where HOUR(TIMEDIFF(endDate, startDate)) < 50

Upvotes: 3

Related Questions