Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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> <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() .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.
Feedback
Do you have any feedback about this page? We'd love to hear it!