Bookmark and Share

Getting around MySQL TIMEDIFF() for hours greater than 838

Posted: Wednesday, March 11th, 2009 at 1:35 pmUpdated: Wednesday, March 11th, 2009 at 4:09 pm

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

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

SELECT TIMEDIFF('2009-03-01 00:00:00', '2009-01-01 00:00:00');
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:

SELECT
	DATEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00') * 24
	+ EXTRACT(HOUR FROM '2010-01-01 00:00:00')
	- 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.

DROP FUNCTION IF EXISTS BIGTIMEDIFF;

DELIMITER $$

CREATE FUNCTION `test`.`BIGTIMEDIFF`
	(end_time VARCHAR(64), start_time VARCHAR(64))
RETURNS INT(10) DETERMINISTIC
BEGIN
	DECLARE ret_val INT(10);

	SELECT
		DATEDIFF(end_time, start_time) * 24
		+ EXTRACT(HOUR FROM end_time)
		- EXTRACT(HOUR FROM start_time)
		INTO ret_val
	;

	RETURN ret_val;
END$$

DELIMITER ;

-- Example calls
SELECT BIGTIMEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00');
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:

SELECT TIMESTAMPDIFF(
	HOUR,
	'2009-01-01 00:00:00',
	'2010-01-01 00:00:00'
);

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.

12 Responses to “Getting around MySQL TIMEDIFF() for hours greater than 838”

  1. mihai1302 Says:

    OK, that’s a great article…

  2. David Says:

    Excellent article. Clear, concise, and well written. Very useful. Thanks.

  3. if only Says:

    if only more developers could communicate and create a tutorial as well as you

  4. gowri Says:

    Really wonderful explanation ! Thank you so much

  5. Matt Says:

    Thanks very much for this information – excellent article.

    I agree wholeheartedly that the MySQL documentation is really lacking regarding argument and return types especially around DATE/TIME functionality.

  6. Arjun Dhar Says:

    Funny thing is when I execute the Query on the console it automatically truncates it to the bounded value and does not complain.

    But when I run it via Code, then MySQL vomits a SQL Error: 1292, SQLState: 22001
    Data truncation: Truncated incorrect time value: ‘842:00:00′

    …I’m still interested to know why the difference; but I think using TIMESTAMPDIFF with say HOUR, is a safer option, as shown in your article.

    thanks

  7. yao Says:

    TIMESTAMP is not waterproof, because in MySQL ,TIMESTAMP has a range of ‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC.

  8. yao Says:

    This function will solve the problem.

    drop function if exists ???.datetimedelta;
    delimiter \\
    create definer=current_user
    function ???.datetimedelta(datetimefirst datetime,datetimesecond datetime,unit varchar(10))
    returns double
    deterministic
    begin
    declare result,result_sec double;
    declare date1,date2 date;
    declare time1,time2 time;
    set date1=(select date(datetimefirst));
    set date2=(select date(datetimesecond));
    set time1=(select time(datetimefirst));
    set time2=(select time(datetimesecond));
    — obtain time difference in seconds
    set result_sec=(select datediff(date1,date2)*86400)+time_to_sec(timediff(time1,time2));
    set result=(select (case unit when ‘sec’ then result_sec
    when ‘min’ then result_sec/60
    when ‘hour’ then result_sec/3600
    when ‘day’ then result_sec/86400
    else ‘wrong’ end));
    return(result);
    end\\
    delimiter ;

  9. hekuel Says:

    Please consider that mysql time functions will fail after 19th Jan, 2038.

    http://ajoeblk.blogspot.in/2012/09/year-2038-bug.html

  10. Jagadish Says:

    Excellent Article, Saved a lot of time. Missing piece in MySQL Manual.

  11. Joe Says:

    Thank you so much! the timestampdiff worked! yay!
    greetings from spain

  12. Daniel Says:

    Thank you sir, you just ended my 5 min frightening time which started when I realized TIMEDIFF() had this value interval limitation!
    >Thanks again!

Leave a Reply


five − 3 =