Ponnarasu Ponnarasu - 1 year ago 129
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

in mysql, it giving result as
. 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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download