Bug #70892 Bug of TIMEDIFF function using JDBC connector
Submitted: 13 Nov 2013 2:07 Modified: 28 Feb 9:23
Reporter: Tom Gao Email Updates:
Status: Can't repeat Impact on me:
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
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:


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


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()){

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().
[28 Feb 9:23] Alexander Soklakov
Posted by developer:
Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html, so this bug will not be fixed there.

With the latest Connector/J 8.0 it's still impossible to get the value as a java.sql.Time because it doesn't support such values range. But it's possible to get the result as a java.time.Duration or as a String:

    public void testBug70892() throws Exception {
        this.rs = this.stmt.executeQuery("select TIMEDIFF('2013-12-09 06:22:01','2013-08-07 05:22:01') as time_used from dual");

        Object obj = this.rs.getObject(1, Duration.class);
        assertTrue(obj instanceof Duration);
        assertEquals("PT838H59M59S", obj.toString());

        assertEquals("838:59:59", this.rs.getString(1));

                "The value '838:59:59' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.",
                () -> this.rs.getObject(1));