Construct a Criteria Api Predicate that overlaps a tsrange column type in Postgresql

Question

I want to create a Java Criteria Api Predicate that performs the equivalent SQL query in Postgres 15 as follows:

SELECT time_range_column FROM public.address_table
WHERE  time_range_column && '[2014-09-23, 2015-09-24]'::tsrange;

I declared the time_range_column in my entity as follows:

@Type(PostgreSQLRangeType.class)
@Column(name = "time_range_column")
private Range\<LocalDateTime\> timeRangeColumn;

My solution is as follows, but returns no results:

String dateTimeString1 = "2014-09-15 00:00:00";
String dateTimeString2 = "2015-09-15 00:00:00";
String dateTimeFormat = "yyyy-MM-dd HH:mm:ss";

DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateTimeFormat);
LocalDateTime d1 = LocalDateTime.parse(dateTimeString1, formatter);
LocalDateTime d2 = LocalDateTime.parse(dateTimeString2, formatter);

String rangeLiteral = "[" + d1 + "," + d2 + ")";

Predicate dateRangePredicate = criteriaBuilder.and(criteriaBuilder.literal(rangeLiteral)
.in(root.<LocalDateTime>get("time_range_column")));

How to create the predicate that overlaps these dates?

Answer 1

TL;DR

:: or && are not part of the JPA Criteria API standard. You could use a native SQL query.

---

If you want to use specific features or functions unique to a database system like PostgreSQL, it may not be possible to use them directly within the JPA Criteria API.

Some queries, you can avoid the headache by specifying the escape sequence completely, but for this query; There is no way to simultaneously escape special types such as overlap (&&), double colon (::), and tsrange (cast(string as tsrange)).

It would be appropriate to create this query using a native query (@Query) instead of the Criteria API, or alternatively; if you are using Hibernate you can create a FunctionContributor for this statement.

Create a FunctionContributor, here we give our function a special name and set our pattern:

(It is a simple function definition approach, it can be made more usable.)

public class PostgreSQLTsrangeOverlapFunction implements FunctionContributor {

    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        functionContributions.getFunctionRegistry().registerPattern(
                "fn_tsrange_overlap",
                "?1 && ?2::tsrange",
                functionContributions
                        .getTypeConfiguration()
                        .getBasicTypeRegistry()
                        .resolve(StandardBasicTypes.BOOLEAN)
        );
    }
}

We use this function with the Criteria API:

public static Specification> getSpecification() {
    return (root, query, cb) -> {
        String dateTimeString1 = "2014-09-15 00:00:00";
        String dateTimeString2 = "2015-09-15 00:00:00";
        String dateTimeFormat = "yyyy-MM-dd HH:mm:ss";

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateTimeFormat);
        LocalDateTime d1 = LocalDateTime.parse(dateTimeString1, formatter);
        LocalDateTime d2 = LocalDateTime.parse(dateTimeString2, formatter);

        Path<Object> field = root.get("time_range_column");
        Expression<String> bound = cb.literal("[" + d1 + "," + d2 + "]");
        Expression<Boolean> function = cb.function(
                "fn_tsrange_overlap",
                Boolean.class,
                field,
                bound);

        return cb.isTrue(function);
    };
}

The class must be then registered via Java ServiceLoader mechanism by adding full name of the class with its packages into the file with name org.hibernate.boot.model.FunctionContributor into the java module’s META-INF/services directory.

You use this specification your query will look like this:

select ... from table t1 
where t1.time_range_column && '[2014-09-15T00:00,2015-09-15T00:00]'::tsrange

It was prepared with Spring Boot 3.2.1 and Hibernate 6.2.7.Final.