Spring data jpa PostgreSql: operator does not exist: timestamp without time zone >= bytea

Question

Use Spring Boot 2.6.6, Spring data JPA, Hibernate 5.6.7.Final, PostgreSql Driver 42.3.3, PostgreSql Server 14.

I have query: SELECT u.* FROM "user" u WHERE ((:createdAtFrom = NULL OR :createdAtTo = NULL) OR (u.birthday BETWEEN :createdAtFrom AND :createdAtTo)).

But it not working.

I got error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= bytea
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I turned on hibernate debug for sql parameters and see next rows:

o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARBINARY] - [null]

Why VARBINARY? I tried java.util.Date, java.time.LocalDateTime - same error. what wrong?

There is demo repo: https://gitlab.com/Tsyklop/jpa-test/-/tree/master

Answer 1

Your statement seems to be missing a CAST so that Postgresql knows that the bind parameters are of the type of the columns they get compared to.

Also comparisons with NULL should always be made with IS NULL. See Why doesn't SQL support "= null" instead of "is null"?

So something like

SELECT u.* FROM "user" u 
WHERE ((:createdAtFrom IS NULL OR :createdAtTo IS NULL) 
OR (u.birthday BETWEEN 
    CAST (:createdAtFrom TO TIMESTAMP) 
    AND CAST (:createdAtTo TO TIMESTAMP))
)

should work.