Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8
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> <includeTypes>DATE</includeTypes> </forcedType> </forcedTypes> </database>
For more information, please refer to the manual's section about custom data type bindings.
Feedback
Do you have any feedback about this page? We'd love to hear it!