Mysql timediff returning two different values

Question

I have one query that I'm testing times with.

I get two different times and this is making me doubt the correctness of my data.

Here are the two examples :

SELECT 
    users.fname as counselor,
    count(session.anum) as students,
    SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(starttime, signintime)))) AS 'AVG Wait Time',
    SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(finishtime, starttime)))) AS 'AVG Session Length'
FROM session
    LEFT JOIN support 
        ON support.session_id = session.session_id
    LEFT JOIN users 
        ON users.username = support.counselor
WHERE session.status = 3 
GROUP BY fname;

This returns :

00:01:17 and 00:00:05

the next query is :

SELECT 
    users.fname as counselor,
    count(session.anum) as students,
    SEC_TO_TIME(AVG(TIMEDIFF(starttime, signintime))) AS 'AVG Wait Time',
    SEC_TO_TIME(AVG(TIMEDIFF(finishtime, starttime))) AS 'AVG Session Length'
FROM session
    LEFT JOIN support 
        ON support.session_id = session.session_id
    LEFT JOIN users 
        ON users.username = support.counselor
WHERE session.status = 3 
GROUP BY fname;

00:01:37 and 00:00:05

Why does this happen and which result set am I supposed to trust? Would love if any one can clear my confusion.

Marc B · Accepted Answer · 2013-03-11 17:55:39Z

3

TIMEDIFF() returns a string, e.g.

TIMEDIFF('13:07:33', '9:5:37') -> 04:01:56

If you average that directly, mysql will try to cast 04:01:56 to an int and end up with avg(04) instead.

which is why you have to time_to_sec, which returns that time value as a simple int: 14516, which CAN be averaged directly.

answered Mar 11, 2013 at 17:55

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RaGe10940 Over a year ago

Ah, so the TIME_TO_SEC version of the query is the most reliable? Would it be best to use TIMESTAMPDIFF() instead?

Mic1780 Over a year ago

This is true. AVG() is used with an int. This takes all numbers that come before an non-number characters and removes everything after them. Example would be 123!456 -> 123. Good job Marc.

RaGe10940 Over a year ago

While I wait to accept the answer, would it be best to use TIMESTAMPDIFF()

Marc B Over a year ago

easiest method is AVG(unix_timestamp(start) - unix_timestamp(end)). that'll convert the datetimes DIRECTLY to plain "seconds" (ints).

RaGe10940 Over a year ago

Might seem like a stupid remark but would it be smart to use unix_timestamp / SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF()))) on all my time differences?

|

Collectives™ on Stack Overflow

Mysql timediff returning two different values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related