MySQL timediff — is it a bug?

Question

This must return 1440 minutes and is working fine:

select abs(round((TIME_TO_SEC(TIMEDIFF('2013-03-13 10:00', 
                                       '2013-03-14 10:00'))/60),2)) ;

(2) same function date changed to 2 ~~years~~ days this returns 50339.98

select abs(round((TIME_TO_SEC(TIMEDIFF('2013-03-12 10:00', 
                                       '2013-03-14 10:00'))/60),2)) ;

(3) same function with date changed to 4 ~~years~~ days and the answer is 50339.98

select abs(round((TIME_TO_SEC(TIMEDIFF('2013-03-10 10:00', 
                                       '2013-03-14 10:00'))/60),2)) ;

Is this a bug?

Nope, it [doesn't](http://sqlfiddle.com/#!2/0da0e/2). BTW, it is `days` and not `years`. — hjpotter92, Mar 16 '13 at 02:20
You have some other problem, this works fine as mentioned by @DreamEater — jurgenreza, Mar 16 '13 at 02:24
What values do you get if you select the component expressions: `SELECT ABS(ROUND((TIME_TO_SEC(TIMEDIFF('2013-03-10 10:00' '2013-03-14'))/60,2)) ardtd, ROUND((TIME_TO_SEC(TIMEDIFF('2013-03-10 10:00' '2013-03-14'))/60,2) AS rdtd, (TIME_TO_SEC(TIMEDIFF('2013-03-10 10:00' '2013-03-14'))/60 AS dtd, TIME_TO_SEC(TIMEDIFF('2013-03-10 10:00' '2013-03-14')) AS td, TIMEDIFF('2013-03-10 10:00' '2013-03-14') AS d;`? — Jonathan Leffler, Mar 16 '13 at 02:35
"This must return 1440 minutes" Actually it doesn't have to. Daylight saving changes can occur on the server which mean that it could return something that is not equal to 1440. — Danack, Mar 16 '13 at 03:47

Danack · Answer 1 · 2013-03-16T04:40:22.147

This must return 1440 minutes and is working fine:

No. There could be a daylight saving change or a leap second change. You can't depend on two times being offset by a certain amount just because you think they should be.

There was something that was odd.

select  
    TIMEDIFF('2000-09-14 09:00', '2000-08-10 10:00'),
    TIMESTAMPDIFF(SECOND, '2000-09-14 09:00', '2000-08-10 10:00');

Gives

838:59:59, -3020400

Which is not possible. The explanation is that TIME values in MySQL have a limit on acceptable values.

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'.

Apparently you should either use one of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers, or use DATEDIFF() if you have actual dates.

MySQL timediff — is it a bug?

1 Answers1