CODEWITHSUNDEEP
javahibernatejpajava-17hibernate-6.x
Naveen Varghese
Naveen Varghese

Reputation: 41

DATE_PART function not working after migrating to hibernate 6

My query is to get the number of days from the difference between two date fields and compare it to a specified value.

  select
      *
    from
        transaction transactio0_ 
    where
        transactio0_.order_status=? 
        and (
            transactio0_.date_start between ? and ?
        ) 
        or DATE_PART(?,transactio0_.date_end-transactio0_.date_start)=100.0;

Since there will be a variable amount of conditions, I am unable to use native query, therefore specification is required.

I have provided the specification condition as below

  spec = spec.and((root, query, builder) -> {
                return builder.equal(
                        builder.function("DATE_PART", Double.class, builder.literal("day"),
                                builder.diff(endDate,startDate )),
                        filter.orderPendingDays());
            });

The query was originally generated as follows. DATE_PART('day',transactio0_.date_end-transactio0_.date_start)

I was using hibernate 5, then after switching to hibernate 6, the following things occurred.

This is how the query is generated after migration. DATE_PART('day',extract(epoch from t1_0.date_end-t1_0.date_start)*1e9)

Currently, an error is being thrown.

Caused by: org.postgresql.util.PSQLException: ERROR: function date_part(unknown, double precision) does not exist

Could someone please explain the issue to me?

Upvotes: 4

Views: 205

Answers (1)

blackout
blackout

Reputation: 11

The only workaround I've found is to convert the dates to epoch time, calculate the difference, and then compare it to the number of days, which are also converted to seconds. It looks ugly but works.

In your case the code will look like this:

builder.equal(
    builder.diff(
        builder.function(
            "date_part",
            Double.class,
            builder.literal("epoch"),
            root.get("dateEnd")
        ),
        builder.function(
            "date_part",
            Double.class,
            builder.literal("epoch"),
            root.get("dateStart")
        )
    ),
    filter.orderPendingDays() * 60 * 60 * 24.0
);

Upvotes: 1

Related Questions