Persisting java.time.LocalTime into oracle with Types.TIME loses fractional seconds precision

Question

I have a behavior with Oracle which I'm not sure is intended or not.

I am playing with the various types of the java date/time API, using them at the JDBC level.

I am trying to persist an instance of java.time.LocalTime into an oracle database.

At the JDBC level, my understanding is that I'm supposed to use :

A JDBC 4.2 compliant driver
PreparedStatement.setObject(int parameterIndex, Object x, int targetSqlType)
Types.TIME as the targetSqlType for a java.time.LocalTime instance

Since oracle doesn't provide a TIME type, I use a TIMESTAMP field.

The problem is if I use Types.TIME at the JDBC level, I'm losing the fractional second precision.

If I use Types.TIMESTAMP, the precision is kept intact.

I'd like to use the JDBC intended standard mapping for LocalTime, if possible with Oracle.

Here the tests showing the behavior :

@Test
public void testSetObjectWithSqlTypeTimestamp() {
    testSetObjectWithSqlType("java.sql.Types.TIMESTAMP", Types.TIMESTAMP);
}

@Test
public void testSetObjectWithSqlTypeTime() {
    testSetObjectWithSqlType("java.sql.Types.TIME", Types.TIME);
}

private void testSetObjectWithSqlType(String sqlTypeLabel, Integer sqlTypeValue) {
    logger.info("Testing setObject with {} (value={})", sqlTypeLabel, sqlTypeValue);

    LocalTime willBeInserted = LocalTime.of(21, 17, 23, 678_987_000);

    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = dataSource.getConnection();
        con.setAutoCommit(false);
        ps = con.prepareStatement("insert into LOCAL_TIME_TABLE (FOO) values (?)");
        ps.setObject(1, willBeInserted, sqlTypeValue);

        ps.executeUpdate();
        con.commit();
    } catch (SQLException e) {
        rollbackQuietly(con);
        throw new RuntimeException("Error inserting test value", e);
    } finally {
        JdbcUtils.closeStatement(ps);
        JdbcUtils.closeConnection(con);
    }

    List<LocalTime> results = jdbcTemplate.queryForList("select FOO from LOCAL_TIME_TABLE", LocalTime.class);
    assertThat(results)
        .containsExactlyInAnyOrder(willBeInserted)
        .hasSize(1);
}

The Types.TIMESTAMP test works fine.

The Types.TIME test has the following error :

 [ERROR] Failures: 
 [ERROR]   LocalTimeSetObjectWithSqlTypeOracleBugTest.testSetObjectWithSqlTypeTime:44->testSetObjectWithSqlType:72 
 Expecting:
   <[21:17:23]>
 to contain exactly in any order:
   <[21:17:23.678987]>
 elements not found:
   <[21:17:23.678987]>
 and elements not expected:
   <[21:17:23]>

This is a spring-boot test (DataSource and JdbcTemplate are provided by spring). I didn't detail all the spring boilerplate code, this didn't seem relevant to the problem; but I'll add it if need be.

My FOO table looks like this :

create table LOCAL_TIME_TABLE (
    FOO timestamp
);

I'm using the following Oracle image : https://hub.docker.com/r/wnameless/oracle-xe-11g-r2

And the following driver (maven coordinates) : com.oracle.database.jdbc:ojdbc10:19.3.0.0

So I guess my question is two fold :

Am I correct in assuming that the intended standard JDBC mapping for LocalTime is : ps.setObject(1, localTime, Types.TIME) ? If Not, what is the supposed intended JDBC way?
Is the Oracle behavior intended? If yes, is it documented somewhere?

Also, this is my first SO question, so any feedback is welcome.

Happy coding!

Douglas Surber · Accepted Answer · 2020-06-01T15:02:05.533

Oracle Database stores TIME values in the Oracle SQL DATE format. Oracle SQL DATE does not store fractional seconds. If fractional seconds are significant then use JDBCType.TIMESTAMP.

The SQL spec is subtle (an understatement). All vendors have slightly different implementations of SQL. The SQL spec is sufficiently flexible that all of the major SQL databases are in compliance, very much including Oracle Database. There are significant differences between the vendors' databases and a casual reading of the SQL spec can be misleading. Oracle Database representing TIME as DATE is allowed as is not storing fractional seconds.

Edited to add: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6

Persisting java.time.LocalTime into oracle with Types.TIME loses fractional seconds precision

1 Answers1