Ponnarasu Ponnarasu - 3 months ago 5
Java Question

illegal hour value error while getting difference between two times using TIMEDIFF in mysql

I am using mysql and Java. when iam executing query to get difference between two times using

TIMEDIFF
in mysql, it giving result as
'40:23:12'
. But when I am using that query in java, exception error coming as:


illegal hour value '40'


SELECT TIMEDIFF(NOW(),last_act_time) FROM user_activity_log WHERE id=1;


Is there any solution for this?

Answer

As per my understanding, this is a bug in MySql version 5.1.6.

Ref: https://bugs.mysql.com/bug.php?id=36051

To solve this issue,the result should be converted to string during the execution of query. This can be achieved by the following ways.

SELECT concat('',TIMEDIFF(NOW(),last_act_time)) FROM user_activity_log WHERE id=1;

The concat will make it string so you can process it as string.

SELECT CAST(TIMEDIFF(NOW(),last_act_time) AS CHAR) FROM user_activity_log WHERE id=1;

as suggested by @eggyal

Comments