Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL TIMEDIFF function not working for long date

Tags:

datetime

mysql

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 enter image description here

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 !

like image 398
Gowri Avatar asked Apr 15 '11 12:04

Gowri


2 Answers

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

like image 98
diEcho Avatar answered Sep 16 '22 18:09

diEcho


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

like image 23
rekaszeru Avatar answered Sep 18 '22 18:09

rekaszeru