MYSQL TIMEDIFF function does not work for a long time

Question

The Mysql Timediff function does not work for me for a long date. Actually I need to get the time difference between the date_time field and now ()

so i used this query

SELECT `date_time`,now(),timediff(`date_time`,now()) FROM `table_datetime` 

I have two lines

date_time 2011-04-25 17:22:41 2011-06-14 17:22:52

my result

Here the result of the first line changes, but not for the second, which always returns

 838:59:59 

constantly ... why he does not give the correct result

Thanks for the help!

Answer 1

instead of TIMEDIFF use DATEDIFF with EXTRACT

 SELECT DATEDIFF('2011-06-14 17:22:52', NOW()) * 24 + EXTRACT(HOUR FROM '2011-06-14 17:22:52') - EXTRACT(HOUR FROM NOW()) 

Thanks @rekaszeru for the useful link

Alternative solution (get the difference in seconds)

 SELECT TIMESTAMPDIFF(SECOND,NOW(),'2011-06-14 17:22:52'); 

Link

EXTRACT

TIMESTAMPDIFF

Answer 2

You should take a look at this problem and find a solution that knows these things.

Answer 3

An alternative solution to this problem is to write a custom function. Keep in mind that the return value is no longer a time object, but a string. Therefore, in MySQL you cannot use it for further calculation:

 DELIMITER $$ DROP FUNCTION IF EXISTS `big_time_diff`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `big_timediff`(s_start VARCHAR(20), s_end VARCHAR(20)) RETURNS TEXT CHARSET latin1 BEGIN DECLARE s_hour VARCHAR(20); DECLARE s_rest VARCHAR(20); SET s_hour = ABS(TIMESTAMPDIFF(HOUR, s_end, s_start)); SET s_rest = TIME_FORMAT(TIMEDIFF(s_start, s_end), ':%i:%s'); RETURN INSERT(s_rest, LOCATE('-', s_rest) + 1, 0, IF(LENGTH(s_hour) > 2, s_hour, LPAD(s_hour, 2, 0))); END$$ DELIMITER ;