MySQL Bugs: #94457: Connector/J ResultSet.getObject( ..., OffsetDateTime.class ) throws

Bug #94457	Connector/J ResultSet.getObject( ..., OffsetDateTime.class ) throws
Submitted:	24 Feb 2019 20:59	Modified:	11 Dec 2020 1:30
Reporter:	Michal Rehacek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	OffsetDateTime

Description:
Connector/J's ResultSetImpl claims support for OffsetDateTime in getObject(), but an attempt to use it results in an exception "Conversion not supported for type".

The code looks like

====
            } else if (type.equals(OffsetDateTime.class)) {
                try {
                    String odt = getString(columnIndex);
                    return odt == null ? null : (T) OffsetDateTime.parse(odt);
                } catch (DateTimeParseException e) {
                    // Let it continue and try by object deserialization.
                }
====

odt has the format like "2019-02-24 20:59:27", but OffsetDateTime.parse() expects something like "2007-12-03T10:15:30+01:00", so it throws. The exception is swallowed, and execution continues to the end of ResultSetImpl, where it throws an exception explaining that the conversion is unsupported.

How to repeat:
Please see the attached source file. It contains a sample database schema and Java code that attempts to load DATETIME and TIMESTAMP colunms as OffsetDatTime. The sample code also has some information about possible fix, both for DATETIME and for TIMESTAMP columns.

The sample code is commented. Note that you need to set the host/username/password in code - see the constants at the beginning of the test class.

Suggested fix:
Loading DATETIME columns as OffsetDateTime doesn't have many options, because there's no timezone information embedded - so an assumption must be made for the offset, UTC being the most reasonable. Loading for DATETIME columns inside ResultSetImpl.getObject( OffsetDateTime.class ) might be done using

----
LocalDateTime local = row.getObject( column, LocalDateTime.class );
return local.atOffset( ZoneOffset.UTC );
----

see the sample code, function loadOffsetDateTimeFromDateTime()

For TIMESTAMP colunms, the conversion must take the connection's time zone settings into account, because the actual data/string received from the server displays the timestamp in the connection's timezone. I tried to make a hacky demonstration how String timestamp in a specific timezone can be converted to OffsetDateTime - see loadOffsetDateTimeFromTimestamp(). Note that this is just a demonstration where half of it is attempting to construct the timezone - which the ResultSetImpl has (for example, in its getTime() method).

Source code demonstrating the problem, and discussion about possible solutions

Attachment: MySQLOffsetDateTimeTest.java (application/octet-stream, text), 7.20 KiB.

Core of the code reading OffsetDateTime. No explanation or discussion - see the full test for that

Attachment: MySQLOffsetDateTimeTestShort.java (application/octet-stream, text), 1.43 KiB.

Hello Michal,

Thank you for the report and test case.

regards,
Umesh

Hi Michal,

Please note that, according to Table B-5 of JDBC 4.2 specification, setObject() allows conversion of OffsetTime to CHAR, VARCHAR, LONGVARCHAR, and TIME_WITH_TIMESTAMP and allows conversion of OffsetDateTime to CHAR, VARCHAR, LONGVARCHAR, TIME_WITH_TIMESTAMP and TIMESTAMP_WITH_TIMESTAMP. Since MySQL doesn't support *_WITH_TIMESTAMP types the only JDBC compliant conversion of OffsetDateTime is a string representation.

We could think about possibility to store OffsetTime/OffsetDateTime to TIME, DATETIME and TIMESTAMP fields, but the original value will be lost there, there is no way to keep the oroginal time zone. We could apply some implicit time zone conversions while storing/retrieving so that the Instant represented by original OffsetTime/OffsetDateTime value equals to Instant represented by the retrieved value, but the OffsetTime/OffsetDateTime won't be equal.

Do you have a reason for this non-JDBC compliant behaviour?

What originally made me even try to load DateTime/Timestamp as OffsetDateTime is that I am working on an application that uses both Postgres and MySQL as a possible database server. Postgres allows saving OffsetDateTime to its TIMESTAMP WITH TIMEZONE type, but it stores the timestamp internally as UTC, regardless of the actual offset specified in the OffsetDateTime that is stored. When loading it again, the OffsetDateTime loaded will represent the same Instant as before it was saved, but the offset will be different. That's the same solution that you describe, so there's already a precedent for it elsewhere.

What made me believe that the conversion is meant to work is that the JDBC driver does attempt to convert DATETIME/TIMESTAMP columns to OffsetDateTime, but the conversion only fails due to a slightly different string representation of the DateTime/Timestamp column, and the format that OffsetDateTime.parse expects.

The conversion of DATETIME/TIMESTAMP to OffsetDateTime representing at leas the same instant would allow to use OffsetDateTime with date-like columns in MySQL. Currently, if an application wants to use OffsetDateTime with MySQL, the options are 1. declare all date columns as VARCHAR (which makes it difficult to write queries that use the column as a date-time value, like SELECT where the date-time column is between two values, with an index on the column) or 2. write a lot of wrappers at runtime that do the conversions from the database types to OffsetDateTime.

I understand that the conversion to the same Instant, with the actual offset being lost, isn't fully JDBC compliant - but even that would be more useful than not having any conversion at all, because currently the Java8 date types aren't easily usable with MySQL.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.23 changelog: 

"Conversion of a MySQL DATETIME or TIMESTAMP value to a Java OffsetDateTime using the getObject(i, OffsetDateTime.class) method failed with a "Conversion not supported for type" error. It was because the OffsetDateTime.parse() method on DATETIME and TIMESTAMP values yielded an unexpected string format. With this patch, conversions between OffsetDateTime and the DATE, TIME, DATETIME, TIMESTAMP, and YEAR datatypes are now possible, and an instant point on the timeline is preserved as such during a conversion, when possible—see {new section in the manual} for details."