Bug #77816 Appropriate datatype at java side is not available to hold Time
Submitted: 23 Jul 2015 12:48 Modified: 24 Jan 2022 10:27
Reporter: Nilesh Akhade Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.36, 5.1.35 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: datatype, time

[23 Jul 2015 12:48] Nilesh Akhade
Description:

Exception in java:
java.sql.SQLException: Illegal hour value '32' for java.sql.Time type in value '32:47:06.

This error causes problem, because at SQL side it is addition of timespan.

How to repeat:

CREATE FUNCTION WEEK_TIME() RETURNS TIME DETERMINISTIC
BEGIN
SELECT TIME_THIS_WEEK into @TILL_YESTERDAY FROM AT_OFFICE;
SELECT TIMEDIFF(NOW(), INTIME) INTO @TODAY FROM UPTIME WHERE DATE(INTIME) = CURRENT_DATE();
RETURN ADDTIME(@TILL_YESTERDAY, @TODAY);
END;

Using JDBC:
String sql = "SELECT WEEK_TIME()";
ResultSet rs = stmt.executeQuery(sql);

while(rs.next()){
 Time time  = rs.getTime(1); //<-- This causes error
 System.out.println("Time_You_are_at office: " + time);
}

Suggested fix:
Causes due to ambiguity in Datatype meaning.
Time means addition of timespan at MySQL side, whereas it is 24 hour clock time at java side.

FIX:
Introduce new DataType TimeInterval at MySQL, which is internally compatible with java TimeSpan
[26 Jul 2015 7:43] MySQL Verification Team
Hello Nilesh,

Thank you for report.

Thanks,
Umesh
[2 Sep 2015 11:29] Alexander Soklakov
I'm changing severity to "Feature request".
[24 Jan 2022 10:27] 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 Connector/J 8.0.23 and above it is possible to retrieve TIME as a java.time.Duration using rs.getObject(1, Duration.class) method.