MYSQL TIMEDIFF function not working for long date

Mysql Timediff function is not working for me for long date.. Actually i need to get the time difference between date_time field to now()

so i used this query

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

I have two rows

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

my result is

Here first row result is changing but not f开发者_运维百科or second one this one always return

838:59:59

constantly ... Why its not giving correct result

Thanks for help !

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 useful link

Alternate Solution ( get the difference in Seconds )

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

Reference

EXTRACT

TIMESTAMPDIFF

You should take a look at this issue, and find a solution knowing those things

An alternative solution for this problem is writing a user defined function. Please keep in mind, the return value is no longer a time-object but a string. So in MySQL you can't 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 ;