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

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.

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.