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()
Mysql Timediff函数对我来说不适合长日期。其实我需要得到date_time字段到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
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 for second one this one always return
这里的第一行结果正在改变,但不是第二行结果总是返回
838:59:59
constantly ... Why its not giving correct result
不断...为什么它没有给出正确的结果
Thanks for help !
感谢帮助 !
3 个解决方案
#1
10
instead of TIMEDIFF
use DATEDIFF
with EXTRACT
而不是TIMEDIFF使用带有EXTRACT的DATEDIFF
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
感谢@rekaszeru提供有用的链接
Alternate Solution ( get the difference in Seconds )
SELECT TIMESTAMPDIFF(SECOND,NOW(),'2011-06-14 17:22:52');
Reference
EXTRACT
TIMESTAMPDIFF
#2
4
You should take a look at this issue, and find a solution knowing those things
你应该看看这个问题,找到解决这些问题的解决方案
#3
0
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:
该问题的另一种解决方案是编写用户定义的函数。请记住,返回值不再是时间对象而是字符串。所以在MySQL中你不能用它来进一步计算:
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 ;
#1
10
instead of TIMEDIFF
use DATEDIFF
with EXTRACT
而不是TIMEDIFF使用带有EXTRACT的DATEDIFF
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
感谢@rekaszeru提供有用的链接
Alternate Solution ( get the difference in Seconds )
SELECT TIMESTAMPDIFF(SECOND,NOW(),'2011-06-14 17:22:52');
Reference
EXTRACT
TIMESTAMPDIFF
#2
4
You should take a look at this issue, and find a solution knowing those things
你应该看看这个问题,找到解决这些问题的解决方案
#3
0
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:
该问题的另一种解决方案是编写用户定义的函数。请记住,返回值不再是时间对象而是字符串。所以在MySQL中你不能用它来进一步计算:
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 ;