Oracle DATE data type

Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

Oracle's DATE data type does not conform to the SQL standard. It is really a TIMESTAMP(0), i.e. a TIMESTAMP with a fractional second precision of zero. The most appropriate JDBC type for Oracle DATE types is java.sql.Timestamp.

Performance implications

When binding TIMESTAMP variables to SQL statements, instead of truncating such variables to DATE, the cost based optimiser may choose to widen the database column from DATE to TIMESTAMP using an Oracle INTERNAL_FUNCTION(), which prevents index usage. Details about this behaviour can be seen in this Stack Overflow question.

Use a data type binding to work around this issue

The best way to work around this issue is to implement a custom data type binding, which generates the CAST expression for every bind variable:

@Override
public final void sql(BindingSQLContext<Timestamp> ctx) throws SQLException {
        render.keyword("cast").sql('(')
              .visit(val(ctx.value()))
              .sql(' ').keyword("as date").sql(')');
}

Deprecated functionality

Historic versions of jOOQ used to support a <dateAsTimestamp/> flag, which can be used with the out-of-the-box org.jooq.impl.DateAsTimestampBinding as a custom data type binding:

<database>
  <!-- Use this flag to force DATE columns to be of type TIMESTAMP -->
  <dateAsTimestamp>true</dateAsTimestamp>
  
  <!-- Define a custom binding for such DATE as TIMESTAMP columns -->
  <forcedTypes>
    <forcedType>
      <userType>java.sql.Timestamp</userType>
      <binding>org.jooq.impl.DateAsTimestampBinding</binding>
      <types>DATE</types>
    </forcedType>
  </forcedTypes>
</database>

For more information, please refer to the manual's section about custom data type bindings.

The jOOQ User Manual : Reference : Data types : Oracle DATE data type

previous : next