Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

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 {
    ctx.render()
       .visit(keyword("cast")).sql('(')
       .visit(val(ctx.value())).sql(' ')
       .visit(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:

XML (standalone and maven)

Programmatic

Gradle (Kotlin)

Gradle (Groovy)

Gradle (third party)

<configuration>
  <generator>
    <database>
      <!-- Use this flag to force DATE columns to be of type TIMESTAMP. This has been deprecated. -->
      <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>
          <includeTypes>DATE</includeTypes>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>

See the configuration XSD, standalone code generation, and maven code generation for more details.

new org.jooq.util.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()

      // Use this flag to force DATE columns to be of type TIMESTAMP. This has been deprecated.
      .withDateAsTimestamp(true)

      // Define a custom binding for such DATE as TIMESTAMP columns
      .withForcedTypes(
        new ForcedType()
          .withUserType("java.sql.Timestamp")
          .withBinding("org.jooq.impl.DateAsTimestampBinding")
          .withIncludeTypes("DATE")
      )
    )
  )

See the configuration XSD and programmatic code generation for more details.

// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.

// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.

generationTool {
  generator {
    database {

      // Use this flag to force DATE columns to be of type TIMESTAMP. This has been deprecated.
      dateAsTimestamp = true

      // Define a custom binding for such DATE as TIMESTAMP columns
      forcedTypes {
        forcedType {
          userType = "java.sql.Timestamp"
          binding = "org.jooq.impl.DateAsTimestampBinding"
          includeTypes = "DATE"
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

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