Bug #83121 PreparedStatement.setObject should accept java.time.OffsetTime/OffsetDateTime

• Submitted: 23 Sep 2016 5:45
• Modified: 23 Sep 2016 18:36
• Reporter: Jules H
• Status: Won't fix
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 6.0
• OS: Any
• CPU Architecture: Any

[23 Sep 2016 5:45] Jules H

Description:
According to table  B-4 of the JDBC4.2 spec as downloaded from http://download.oracle.com/otn-pub/jcp/jdbc-4_2-mrel2-spec/jdbc4.2-fr-spec.pdf, java.time.OffsetTime objects should be accepted as TIME_WITH_TIMEZONE values and java.time.OffsetDateTime should be accepted as TIMESTAMP_WITH_TIMEZONE, but looking at the source code for PreparedStatement.setObject() at https://github.com/mysql/mysql-connector-j/blob/release/6.0/src/main/java/com/mysql/cj/jdb... line 3451 these types are not recognized.

How to repeat:
Call PreparedStatement.setObject(int, Object) with a java.time.OffsetTime or java.time.OffsetDateTime value as the object in order to store in a time/timestamp field respectively.

[23 Sep 2016 8:45] Jules H

As an extension, it would be nice to support java.time.Instant also (probably by converting it to an OffsetDateTime with a UTC time zone).

[23 Sep 2016 18:36] Filipe Silva

Hi Jules,

Thank you for taking the time to write this report.

At the time being we cannot support the *_WITH_TIMEZONE types in Connector/J as there is no way of storing the time zone information for temporal data types in MySQL server. Although we could implement some kind of workaround and convert these object to some other type we support, the fact is that we would be loosing information we couldn't guarantee the correct data between storing and retrieving operations.

So, whatever it is in our power to do, it is better done in the client application where you have full control of the kind of data you send and expect and any transformations you wish to apply.

Actually we do have some kind of support for this (and any other Serializable object). You can use the method PreparedStatment#setObject(int, Object) with these objects as long as the table column accepts raw binary data, such as any of the BLOB variants. Obviously you won't be able to use this data in you queries but for some cases it may work.