Migration to SpringBoot 3: trunc a date with oracle driver no longer works (hibernate)

Question

I am currently migrating a SpringBoot 2.7 application to SpringBoot 3. The following query is used within a SpringData repository:

@Query("select b from #{#entityName} b where (trunc(b.date) <= trunc(:date))")
List<T> findByDate(LocalDateTime date);

While this works great in SpringBoot 2.7, with SpringBoot 3 the following message is thrown:

org.hibernate.QueryException: Parameter 1 of function trunc() has type NUMERIC, but argument is of type java.time.LocalDateTime

Unfortunately, a simple migration to datetrunc was unsuccessful:

Error Msg = ORA-00904: "DATETRUNC": ungültige ID

Does anyone have a solution for this?

Best regards

Uninterested

strange because TRUNC is registered as standard SQL function without a static type in all Dialect, not only the ORACLE ones. – Frown

Thank you for your message. Can you please explain that in more detail? – Uninterested

If you look at the source code of the dialects, you will see that the SQL functions of each one are registered with either a specific type either with none because they accept different parameter types and this is the case for "trunc", why it's very strange to get that error. – Frown

Thanks :) .. looks like a bug. Assumption by beikov (hibernate team member): “It seems that the trunc function has multiple overloads but Hibernate 6 only supports the numeric variant right now” I created a issue hibernate.atlassian.net/browse/HHH-15855 – Uninterested

Indeed in that case it's a bug. – Frown

It was but it has been closed. There was recommended to use date_trunc() but date_trunc() isn't even a Oracle function at all. I assume a lot of people use H2 database for testing things which actually run on Oracle databases. So why removing H2 functions which were already existing an working? – Elianore

Answer 1

So, as I noted in my previous answer, there's no standard HQL function for date truncation. That's because it would be quite difficult to implement in most SQL dialects (I have not really investigated to see quite how difficult, but it's at least nontrivial.)

However, just especially for you, in Hibernate 6.2, which should go into CR today, what I have done is added undocumented (but tested) support for the date_trunc() function under Postgres, DB2, Oracle, and H2. On Oracle, this translates to trunc(), of course.

For example, you could write:

date_trunc(year,current_timestamp)

When I say it's "undocumented" I mean you use it at your own risk. (The documented way to do this remains to use a FunctionContributor.)

I hope that helps.

UPDATE:

Oh and by the way, I just noticed that you're not actually using the full form of the trunc() function in your query. You're actually just stripping off the time part of a timestamp.

There's actually multiple ways to do that in HQL without needing to use the Oracle-specific trunc() function. (But of course trunc()/date_trunc() can do more things that you're not using here.)

JPA-standard way

One of the improvements I added to the new JPA 3.1 specification was to let you write:

extract(date from current_timestamp)

To strip off the time part of the timestamp.

HQL alternative

But you can also do it using the HQL cast() function if you prefer:

cast(current_timestamp as Date)

These two options translate to the same SQL on Oracle.

Spinthariscope

"just especially for you"... I have read your proposals for jakarta persistence (for example github.com/jakartaee/persistence/issues/318), that is embarrassing that jakarta team have no will to improve standard, thank you for everything what you are doing for HBN. – Selfmoving

The documented FunctionContributor way doesn't seem to work if date is null: java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.mapping.SqlExpressible.getJdbcMapping()" because the return value of "org.hibernate.sql.ast.tree.expression.Expression.getExpressionType()" is null" even tho 'select trunc(null) from dual' works perfectly fine in Oracle. I'm not sure why native TRUNC support was removed for OracleDialect -- seems to cause unnecessary work. – Crucifixion

@Gravin King cast(current_timestamp as Date) Still has time component in oracle. What other ways do I have to strip off time component? – Orpiment

Nesting functions like NVL also don't work. E.g., extract(date from NVL(start_dt, sysdate-1)) gives error: org.hibernate.QueryException: Parameter 2 of function extract() has type TEMPORAL, but argument is of type java.lang.Object – Crucifixion

have the same issue as Arun, when using this approach for comparisons, it does not remove the time component. So if a date is set to 31.12.xxxx and 01.01.xxxx+1 and I check this on the 31.12.xxxx with the extract Function it finds no result, because time is still taken into consideration. Should I send you a reproducer? – Membership

Hopefully you found the above answer from Michal. Just adding the additional argument to TRUNC worked for us: TRUNC(t.creation_date, DAY) – Alameda

None of these work for me with a timestampwithtimezone column and a spring datajpa entity with a field of type Instant – Brougham

Answer 2

NOTE: Check out my other answer if you have newer hibernate, it's way less hacky ;)

I'm using Oracle database and after migration to new hibernate I had the same issue.

I solved it by overriding org.hibernate.dialect.OracleDialect and adding ORACLE_TRUNC function to a functionRegistry. I've used the same definition trunc function definition as in Hibernate < 6 Oracle8iDialect.

public class CustomOracleDialect extends OracleDialect {

    public CustomOracleDialect() {
        super();
    }

    @Override
    public void initializeFunctionRegistry(FunctionContributions functionContributions) {
        super.initializeFunctionRegistry(functionContributions);

        functionContributions.getFunctionRegistry().register("ORACLE_TRUNC", new StandardSQLFunction("trunc"));
    }

}

Configure hibernate to use this dialect inapplication.properties / application.yml file:

spring:
  jpa:
    database-platform: com.example.CustomOracleDialect

In your queries use ORACLE_TRUNC() instead of TRUNC().

I tried using DATE_TRUNC(date, my_date) function from hibernate 6.2 RC2 as Gavin King said - but it transformed my query to CAST(my_date AS DATE) instead of TRUNC(my_date) causing date comparison issues.

Aerialist

We have the same issue after upgrading hibernate from 5.6.15.Final to 6.5.2.Final. I have to use this method to get the application working. Hope Hibernate will release an actual fix soon. – Acarid

Answer 3

In Hibernate 6 we started checking the argument types of HQL functions. Since trunc(numeric,places) is a very common function across many dialects of SQL, we register it as one of the known functions, though we have not yet promoted it to a “standard” HQL function (perhaps we should).

On the other hand, Oracle’s trunc(date) is extremely specific to Oracle and is more like the date_trunc() function on DB2 and Postgres. We have not (so far) attempted to standardize on any sort of function for timestamp truncation, and I don’t consider it a high priority. This function is not registered by OracleDialect.

So, therefore, if you try to call trunc() on a Date, you will get a typing error.

Now, in general, in Hibernate 6.x:

• We do not guarantee that Hibernate Dialects know about every single SQL function in your database, and you can't depend on that.
• Instead we have a rather long list of documented HQL functions that we promise work portably across every database we support. (Right now trunc() is not on that list, but it almost made it onto the list.)

And that's perfectly fine, because we also provide APIs that make it very easy to register new SQL functions, either platform-specific functions like this one, or functions you've written yourself, either by:

• writing a custom Dialect, or
• providing a FunctionContributor.

https://docs.jboss.org/hibernate/orm/6.1/userguide/html_single/Hibernate_User_Guide.html#hql-user-defined-functions

Alternatively, if that's too much work, you can just use the JPA-standard syntax for calling native SQL functions, which is function('trunc', date, 'YEAR'), but I consider this a bit unlovely.

Spinthariscope

Thanks for the explanation! The FunctionContributor solution sounds as a good solution to this. As someone who is also affected by this I gotta say that its a bit weird to see this working on previous Hibernate versions and suddenly "broken" - wouldnt it make sense to add it to the oracle dialect? Or maybe a follow up question - do you know if the standardization of timestamp truncation is something that is on the "road map" for the future? – Rotherham

Well, no, it's not weird at all: it's an important new feature of Hibernate 6! I've been pretty vocal about how the whole philosophy in HQL has changed from "just let shit go through to the database" to "let's make sure we're sending something sensible". I talked about it here, for example: youtube.com/watch?v=pc6QIwx0EL0&t=1564s – Spinthariscope

As to making it a standard function in HQL, well, we can't really, since most databases don't support anything similar. However, see the second answer I'm about to post. – Spinthariscope

Answer 4

I found a very easy way to replace the trunc function but it's kind of ugly.

to_date(to_char(entity.date,'YYYY-MM-DD'), 'YYYY-MM-DD')

if it can help someone while waiting for a better jpql solution...

Cauvery

Answer 5

After debugging org.hibernate.dialect.OracleDialect in hibernate-core:6.4.1 I found out that Oracle TRUNC() function is actually implemented for dates (org.hibernate.dialect.function.OracleTruncFunction).

In order to get it to work you must to use 2 argument function where first argument is the date, and the second one is granuality (org.hibernate.query.sqm.TemporalUnit). TRUNC() with single parameter is used for NUMERIC types.

So for example in JPQL this works:

SELECT t FROM MyTable t WHERE TRUNC(t.creation_date, DAY) = TRUNC(current_timestamp, DAY);

And it will transform your query to something like this:

select ... where trunc(ad2_0.creation_date,'DD')=trunc(current_timestamp,'DD');

Aerialist

Answer 6

I had to solve this with CriteriaBuilder this way:

cb.function("date_trunc", java.sql.Date.class, cb.literal("DD"), root.get("myAttribute"))

Where root is an instance of jakarta.persistence.criteria.Root for your entity and myAttribute the attribute of type Timestamp you want to truncate.

Guadalupeguadeloupe

Answer 7

I had the same issue while migrating to Spring Boot 3 and Hibernate 6.2.5.Final, Use

TO_DATE(TO_CHAR(:date,"dd-mm-yyyy"))

in place of

trunc(:date)

Edin