Using Java 8 Dates with Spring JPA and PostgreSQL for Custom Queries
Storing dates in databases was traditionally done with java.sql.Date and java.sql.Timestamp.
Since we would like to write modern applications, with Java 8 or 11, it is preferable to use an OffsetDateTime or a LocalDateTime.
Spring Data JPA, for example, is mapping those fields automatically to the correct database field.
There are still some challenges in combination with PostgreSQL.
Behavior of the PostgreSQL Driver
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
Entering the JPA Date Type Fight
In case you would like to provide an optional date field to a JPA query, you are facing an interesting problem with the following JPQL statement:
select p from Persons p where (:createdAt is null or p.createdAt > :createdAt)
This will fail with the following exception:
org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
Most of the results are that you should cast it to a timestamp with ::timestamp or ::timestamptz.
Actually, those are not working with JPQL, since there you need to use the JPQL syntax for the cast: cast(:createdAt as timestamp).
The next obvious solution would be to add casts to both of them:
select p from Persons p where (cast(:createdAt as timestamp) is null or p.createdAt > cast(:createdAt as timestamp))
This will work in case you are providing a date, but it will fail in case :createdAt is null:
org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone
Position: 378
Taming the JPA Date Type Biest
The not really obvious solution is to cast only the types on this places where PostgreSQL on the server is not able to automatically detect the cast:
select p from Persons p where (cast(:createdAt as timestamp) is null or p.createdAt > :createdAt)
For the is null it's not obvious which type it is, but for the date comparison, it can just detect the type by using the type on the left side.
Conclusion
There are a lot of challenges with JPA during handling date times. The issues mentioned here are related to the specific implementation of the PostgreSQL driver. In the unit tests with an embedded H2 database, we were not able to reproduce those issues. You might experience similar issues with JDBC.