Postgres Sql `could not determine data type of parameter` by Hibernate

The PostgreSQL driver tries to figure out the type of the parameters to tell those parameters directly to the PostgreSQL Server. This is necessary that the PostgreSQL server is able to compare fields. In case of a java.sql.Timestamp the PostgreSQL driver is just not able to do it since there are two matching fields for PostgreSQL. On the one side, there is the timestamp and on the other side the timestamptz with timezone information. The result is, that the PostgreSQL driver will just match it to Oid.UNSPECIFIED. This behavior is most of the time not an issue since the PostgreSQL server is able to detect the type. There is a detailed description of this issue in the PostgreSQL driver class PgPreparedStatement.

So what you can do is force cast to timestamp/date type only when Postgres is not able to detect proper type (when you are checking for null).So instead of

(:fromDate is null )

use

(cast(:fromDate as date) is null )

same for toDate

By my experience, you must assign a type to the outer value before you can ask postres to check whether it is null or not. For example, this code won't work for postgres:

SELECT * FROM table WHERE $1 IS NULL OR column = $1;

And what would work is:

SELECT * FROM table WHERE $1::text IS NULL OR column = $1::text;

You can try applying the correct type to your variables to see if it works out. In your example, you should try

@Query(    value = "SELECT new ir.server.component.panel.response." +            "InvalidCardReaderDataDigestResponse(" +            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +            "    cr.transactionDate, cr.offLoadingDate, " +            "    cr.cardReaderNumber, " +            "    sum(cr.count) , sum(cr.remind) " +            ") " +            "FROM CardReaderEntity cr " +            "WHERE cr.company.id = :companyId " +            "AND cr.status.id in :statusIds " +            "AND cr.deleted = false " +            "AND  (:fromDate::timestamp is null or cr.offLoadingDate >= :fromDate::timestamp ) " +            "AND  (:toDate::timestamp   is null or cr.offLoadingDate <= :toDate::timestamp   ) " +            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber")

In fact, postgres won't ask for types in most of the cases, checking null value is one of the special cases that type becomes necessary.