Bug #76775 Fractional seconds in TIME values are not available via JDBC
Submitted: 21 Apr 2015 13:45 Modified: 26 Nov 2020 21:34
Reporter: Sergey Savenko Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.35 OS:Any
Assigned to: CPU Architecture:Any

[21 Apr 2015 13:45] Sergey Savenko
Description:
Fractional seconds in TIME values are not set in java.sql.Time values obtained via JDBC.

How to repeat:
Run the following:

create table fractional_time (t TIME(6));
insert into fractional_time VALUES ('10:10:10.000843');

Query this table via JDBC, obtain a java.sql.Time object.

Suggested fix:
Use java.sql.Time#Time(long) constructor in Connector / J to pass all available data
[23 Apr 2015 8:52] Filipe Silva
Hi Sergey,

Thank you for this bug  report.
[23 Apr 2015 10:34] Filipe Silva
You are right, in Connecor/J java.sql.Time values don't have fractional seconds, but it is expected to be so.

Please check the implementation of the class java.sql.Time. You'll see that there is nothing prepared to deal with the fractional seconds part. See how toString() or, even better, the new toLocalTime() methods are implemented for example. They entirely discard the fractional seconds part.

Now, considering that the only non-deprecated constructor for this class accepts a millisecond time value, we could actually store the fractional seconds part rounded at milliseconds. The value of this is arguable but, in any case, I think this report should change to S4 (Feature request). Please let me know if you agree.

We should discuss internally this change you are proposing. It would help if you could send us your rationale behind it and why you absolutely need it.

Thank you,
[23 Apr 2015 11:20] Sergey Savenko
I guess, we can change it to S4.

As to the rationale behind this request, I'm writing an application which allows for display and editing of database tables' data, and some users are complaining there is no fractional part in their Time values.

Rounding fractional part at milliseconds is slightly better than dropping it off entirely, in my opinion, as it will allow for correct display/editing of Time values with fsp of 0 to 3. Although most of java.sql.Time's methods are not using the milliseconds part of value, there is java.util.Date#getTime, which allows to make use of it.
[25 Apr 2015 13:53] MySQL Verification Team
Hello Sergey,

Thank you for the feature request.

Thanks,
Umesh
[26 Nov 2020 21:34] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.23 changelog: 

"Connector/J's implementation of the java.sql.TIME class now stores fractional part of a second after rounding it off to the nearest millisecond. Before this release, TIME does not store any fractional seconds."
[8 Dec 2020 20:30] Daniel So
Posted by developer:
 
Corrected the changelog entry to the following: 

"While a java.sql.TIME instance, according to the JDBC specification, is not supposed to contain fractional seconds by design, because java.sql.TIME is a wrapper around java.util.Date, it is possible to store fractional seconds in a java.sql.TIME instance. However, when Connector/J inserted a java.sql.TIME into the server as a MySQL TIME value, the fractional seconds were always truncated. To allow the fractional seconds to be sent to the server, a new connection property, sendFractionalSecondsForTime, has been introduced: when the property is true (which is the default value), the fractional seconds for java.sql.TIME are sent to the server; otherwise, the fractional seconds are truncated.

Also, the connection property sendFractionalSeconds has been changed into a global control for the sending of fractional seconds for ALL date-time types. As a result, if sendFractionalSeconds=false, fractional seconds are not sent irrespective of the value of sendFractionalSecondsForTime."