MySQL Bugs: #77816: Appropriate datatype at java side is not available to hold Time

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

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

Hello Nilesh,

Thank you for report.

Thanks,
Umesh

I'm changing severity to "Feature request".

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.