Getting around MySQL TIMEDIFF() for hours greater than 838

One of the golden rule of programming is to know about the function you are calling. You need to know what parameters it takes and what return values and type it gives back. For the most part, it’s not too hard to find that out. For MySQL, however, I found that it’s not always easy to find what the returned type is.
For example, from MySQL documentation about EXTRACT() function,

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

The documentation fails to tell the type of the returned value. I am not sure, but I believe it’s either INT or BIGINT.
Another example is MySQL TIMEDIFF() function documentation:

TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

The documentation doesn’t clearly say that it returned TIME type. However, if you do execute it, it returns TIME type. And from MySQL documentation about TIME type:

TIME values may range from ‘-838:59:59′ to ‘838:59:59′.

So for example, if you do

1.SELECT TIMEDIFF('2009-02-01 00:00:00', '2009-01-01 00:00:00');

As expected, you’ll get 31 * 24 = 744 hours. MySQL will return you 744:00:00. Now, let’s try another query

1.SELECT TIMEDIFF('2009-03-01 00:00:00', '2009-01-01 00:00:00');

2.SELECT TIMEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00');

You would expect to get (31 days in Jan + 28 days in Feb) * 24 = 1416 hours. The 2nd query, you’d expect 365 * 24 = 8760 hours. However, if you run the queries above, MySQL will return 838:59:59. The reason is precisely because TIME type in MySQL has an upperbound of 838:59:59 as mentioned above.

So what workaround can we do to get around this limitation?

That depends on what you need. If you just need to get the hour difference between 2 dates, then you can work around it by calculating:
Number of days * 24 + time difference.
So the SQL query above will need to be re-written like below:

1.SELECT

2.DATEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00') * 24

3.+ EXTRACT(HOUR FROM '2010-01-01 00:00:00')

4.- EXTRACT(HOUR FROM '2009-01-01 00:00:00')

On the SQL above, we first get the number of days between 2 dates, multiply it by 24 (since there are 24 hours in a day) then add the difference. The result is an INT instead of TIME.
If you insist on returning the minutes and seconds as well, the only option for you is to return the string ‘8760:00:00′. In that case, all you have to do is simply append the minutes and second to the result above.
Off course if you do this often, it may be better to define a function for it.

01.DROP FUNCTION IF EXISTS BIGTIMEDIFF;

02. 

03.DELIMITER $$

04. 

05.CREATE FUNCTION `test`.`BIGTIMEDIFF`

06.(end_time VARCHAR(64), start_time VARCHAR(64))

07.RETURNS INT(10) DETERMINISTIC

08.BEGIN

09.DECLARE ret_val INT(10);

10. 

11.SELECT

12.DATEDIFF(end_time, start_time) * 24

13.+ EXTRACT(HOUR FROM end_time)

14.- EXTRACT(HOUR FROM start_time)

15.INTO ret_val

16.;

17. 

18.RETURN ret_val;

19.END$$

20. 

21.DELIMITER ;

22. 

23.-- Example calls

24.SELECT BIGTIMEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00');

25.SELECT BIGTIMEDIFF(CURDATE() + INTERVAL 1 YEAR, NOW());

UPDATE:
Another possibility is to run a TIMESTAMPDIFF() function instead of TIMEDIFF(). Here’s a totally equivalent call to the BIGTIMEDIFF() custom function above:

1.SELECT TIMESTAMPDIFF(

2.HOUR,

3.'2009-01-01 00:00:00',

4.'2010-01-01 00:00:00'

5.);

Note that TIMESTAMPDIFF, the dates are reversed. With TIMESTAMPDIFF() function, other than getting HOUR you can also specify FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
There you have it … I hope you enjoyed this article. Please leave comments / suggestions / questions if you have. I’m looking forward to improving my solution with your comments / suggestions / questions.