Using TIMESTAMPDIFF function on LocalDate in JPA Criteria query

Question

I have to write a query calculating the time difference in years between two dates. One of the dates is fetched from the database whereas the other is a LocalDate. Following is the code I have written:

Expression<String> year = new UnitExpression(null, String.class, "YEAR");
LocalDate date=LocalDate.of(2018, 04, 30);
Expression<Integer> timeDiff = builder.function(
            "TIMESTAMPDIFF",
            Integer.class,
            year ,
            propertyListRoot.get("rentStartDate"),
            date);

where UnitExpression is a custom class extending BasicFunctionExpression . However, this gives a compile time error saying

Required type Expression<?> Provided LocalDate

I researched a bit and tried

criteriaBuilder.literal(date)

This resolves the compile time error but doesn't compute the right value. What can be a possible solution? What am I missing here?

score 0 · Accepted Answer · answered Jul 02 '21 at 07:36

The mistake I was making was not converting the arguments of the function to Timestamp. This works fine:

Expression<Integer> timeDiff = builder.function(
            "TIMESTAMPDIFF",
            Integer.class,
            year,
            propertyListRoot.<Timestamp>get("rentStartDate"),
            builder.literal(Timestamp.valueOf(month))
    );

Using TIMESTAMPDIFF function on LocalDate in JPA Criteria query

1 Answers1