Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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:
<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>
<name>TIMESTAMP</name>
<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.meta.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()
.withName("TIMESTAMP")
.withUserType("java.sql.Timestamp")
.withBinding("org.jooq.impl.DateAsTimestampBinding")
.withIncludeTypes("DATE")
)
)
)
See the configuration XSD and programmatic code generation for more details.
import org.jooq.meta.jaxb.*
configuration {
generator {
database {
// Use this flag to force DATE columns to be of type TIMESTAMP. This has been deprecated.
isDateAsTimestamp = true
// Define a custom binding for such DATE as TIMESTAMP columns
forcedTypes {
forcedType {
name = "TIMESTAMP"
userType = "java.sql.Timestamp"
binding = "org.jooq.impl.DateAsTimestampBinding"
includeTypes = "DATE"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
configuration {
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 {
name = "TIMESTAMP"
userType = "java.sql.Timestamp"
binding = "org.jooq.impl.DateAsTimestampBinding"
includeTypes = "DATE"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19. // Please use the official plugin instead of the third party plugin that was recommended before.
For more information, please refer to the manual's section about custom data type bindings and forced types.
Feedback
Do you have any feedback about this page? We'd love to hear it!