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

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.

Answer 3

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.

Answer 4

I found a very easy way to replace the trunc function but it's kinda of 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...