CODEWITHSUNDEEP
javapostgresqlhibernatejpacriteria-api
Lisek
Lisek

Reputation: 783

JPA Criteria API + PostgreSQL filter by date with minute precision

Due to a well known Hibernate+Postgres bug I decided to use Criteria API as many of my arguments can be null (fed from REST call).

As Criteria API behaves differently with each db client I can not get the solution when it comes to dates (yyyy-MM-dd HH:mm:ss format). Anything that worked for me has not been satisfying as either I am loosing precision or I am getting exceptions. To the point:

Here is the field/column definition from my entity which maps to timestamp column type in Postgres Database:

@Column(name = "last_modified_date",
        nullable = false,
        updatable = false,
        columnDefinition = "TIMESTAMP WITHOUT TIME ZONE")
@Temporal(TemporalType.DATE)
protected Date lastModifiedDate;

Here is the the JpaRepository interface where I use CriteriaAPI:

public interface PageHistoryRepository extends JpaRepository<PageHistoryEntity, Long>, JpaSpecificationExecutor {

default Page<PageHistoryEntity> findPages(Date fromDate,
                                          Date toDate,
                                          Pageable pageable) {
    return findAll(search(fromDate, toDate), pageable);
}

static Specification<PageHistoryEntity> search(Date fromDate, Date toDate) {
    return (root, cq, cb) -> {
        //To ensure we start with a predicate
        Predicate predicate = cb.isTrue(cb.literal(true));

        // this works but has no time precision
        if (fromDate != null) {
            Predicate _predicate = cb.greaterThanOrEqualTo(root.get(PageHistoryEntity_.lastModifiedDate.getName()).as(java.util.Date.class), fromDate);
            predicate = cb.and(predicate,_predicate);
        }

        // this works aswell but again looses time precision
        if (toDate != null) {
            Predicate _predicate = cb.lessThanOrEqualTo(root.get(PageHistoryEntity_.lastModifiedDate.getName()), toDate);
            predicate = cb.and(predicate,_predicate);
        }

        // this does not work
        if (toDate != null) {
            Predicate _predicate = cb.lessThanOrEqualTo(
                    cb.function("date_trunc", Calendar.class, cb.literal("minute"), root.get(PageHistoryEntity_.lastModifiedDate.getName()).as(Calendar.class)),
                    cb.function("date_trunc", Calendar.class, cb.literal("minute"), cb.literal(toDate)).as(Calendar.class));
            predicate = cb.and(predicate,_predicate);
        }

        return predicate;
    };
}

}

The last of the predicates that I was hoping for it to work throws exception:

org.postgresql.util.PSQLException: ERROR: function date_trunc(character varying, unknown) is not unique
  Wskazówka: Could not choose a best candidate function. You might need to add explicit type casts.

The format of the incoming date is fine, there is the value while debugging (it corresponds to the value sent from Postman so all is ok):

debug

postman

Here I also provide screen how the data looks in Postgres:

postgres

My question here is, how can I filter my search results by date knowing that the user might not provide any of dates or just one of them or both of them using Criteria API+PostgreSQL? Ideally I would like to my last Predicate that uses date_trunc to work but this is too ambitious for me now.

Upvotes: 1

Views: 1491

Answers (1)

Lisek
Lisek

Reputation: 783

I guess I experimented not enough, the proper way to build such criteria that would retain the time precision:

if (toDate != null) {
    Predicate _predicate = cb.lessThanOrEqualTo(
            cb.function("date_trunc", Date.class, cb.literal("minute"), root.get(PageHistoryEntity_.lastModifiedDate.getName())),
            toDate);
    predicate = cb.and(predicate,_predicate);
}

Upvotes: 2

Related Questions