Bug #70892 Bug of TIMEDIFF function using JDBC connector
Submitted: 13 Nov 2013 2:07 Modified: 18 Nov 2013 10:32
Reporter: Tom Gao Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.27 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: connector, jdbc, timediff

[13 Nov 2013 2:07] Tom Gao
Description:
In j2ee environment,
when I use timediff to calculate the time difference of two time, the result will be converted to type of 'java.sql.Time'

How to repeat:
	ResultSet result = 	getConnection().prepareCall("select TIMEDIFF('2013-12-09 06:22:01','2013-08-07 05:22:01') as time_used  from dual").executeQuery();
	while (result.next()) {
		System.out.println("current is" + result.getObject(1));
	}
It wil throw out a sql exception error as below:
---------------------------------------------------
	java.sql.SQLException: Bad format for Time '838:59:59' in column 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:1012)
	at com.mysql.jdbc.ByteArrayRow.getTimeFast(ByteArrayRow.java:226)
	at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:6171)
	at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5903)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:5051)
	at com.mchange.v2.c3p0.impl.NewProxyResultSet.getObject(NewProxyResultSet.java:2365)
	at com.active.security.service.TestTimeDiff.selectTimeDiff(TestTimeDiff.java:16)
	at com.active.security.controller.common.LoginController.index(LoginController.java:78)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)

Suggested fix:
I'm not sure about how to fix it. But I just try to used it as below:

CONCAT(TIMEDIFF('2013-12-09 06:22:01','2013-08-07 05:22:01'),'')  , I use function-'CONCAT' to convert it to string. Then it won't be converted to java.sql.Time
[13 Nov 2013 5:39] Todd Farmer
Hi tom tom,

Thanks for the bug report.  The problem you cite isn't specific to TIMEDIFF - it seems to be generic behavior for handling TIME data types in MySQL.  These data types can have values from '-838:59:59.000000' to '838:59:59.000000', per the documentation:

http://dev.mysql.com/doc/refman/5.6/en/time.html

However, java.sql.Time objects do not allow hours which exceed 23:

http://docs.oracle.com/javase/6/docs/api/java/sql/Time.html

The test case you provided can be simplified to the following:

		ResultSet rslt = c.createStatement().executeQuery("SELECT CAST('838:59:59' AS TIME) t");
		while (rslt.next()){
			System.out.println(rslt.getString(1));
		}

The same SQLException will be presented.  Looking at the code in com.mysql.jdbc.ResultSetImpl.getTimeFromString(), there appears to be the following check which throws this SQLException message:

					if ((timeAsString.length() != 5)
							&& (timeAsString.length() != 8)) {
						throw SQLError.createSQLException(Messages
								.getString("ResultSet.Bad_format_for_Time____267") //$NON-NLS-1$
								+ timeAsString
								+ Messages.getString("ResultSet.___in_column__268")
								+ columnIndex, SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
					}

Allowing only string lengths of 5 ("12:45") or 8 ("12:45:23") causes problems beyond simply limiting the range of values in a TIME column; it also adversely affects new fractional second capabilities of MySQL Server 5.6.  A value of "12:34:56.123456" triggers the same SQLException.  I think that's definitely a bug needing resolution.

Of course, it's possible to use string lengths which pass the check listed above, but which don't comply with java.sql.Time range limitations.  Using a value of "25:12:34" will produce a different SQLException and stack trace:

java.sql.SQLException: Illegal hour value '25' for java.sql.Time type in value '25:12:12.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
	at com.mysql.jdbc.TimeUtil.fastTimeCreate(TimeUtil.java:1050)
	at com.mysql.jdbc.ResultSetImpl.fastTimeCreate(ResultSetImpl.java:1058)
	at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:6114)
	at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5845)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692)

I'm unclear how we should expect Connector/Java to handle MySQL TIME data types which exceed limits of java.sql.Time objects, but I'll check with those more familiar with the JDBC specification.
[13 Nov 2013 6:39] Tom Gao
Sounds great. The function - 'TIMEDIFF' runs well in mysql management tool so I think there should be an error of Connector/J.
Looking forward to new information about it and thanks for your advice:)
[13 Nov 2013 6:46] Tom Gao
BTW, it seems that the problem located in the function - 'TIMEDIFF' because I use it to calculate how much time my task used. And 'TIMEDIFF' should return a value as string like '25:10:10' (it means '25hours,10minutes,25 seconds')
But when I tried to get the value, it was converted to java.sql.Time(why not string?) and then it throws out an exception.
Hope authors  of Connector/Java could help review that.

Thanks a lot.
[13 Nov 2013 14:17] Mark Matthews
For those interested in the internals, look at the bottom of the stack:

	at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:6114)
	at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5845)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692)

That means the *server* is telling the client this is a SQL TIME type, when really, it isn't. I'm not sure there's an easy fix for this, it will have to be something like try to handle it as a TIME type, if it's not conformant, return as a VARCHAR.
[14 Nov 2013 7:53] Tom Gao
Thanks Mark.

I agree with you, what we need should be a result of time difference as varchar type, not java.sql.Time.
[27 Mar 2018 10:58] Dmitriy Tseyler
Hi! Any news on this? Please, add an ability to at least get the value as string via rs.getString().