PostgresSQL + Spring JPA: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone

user_13857910 0
Viewed 3620

how to select rows based on date range when sometimes the date range might have null values (select all rows)

I have a table called Project(id, name, certification_date); id is int, name is varchar(250) and certification_date is timestamp.

When a project is inserted then, only id and name is inserted, but certification_date is null. Later the user can certify the project then the row is updated with certification_date with the date the user certifies the project. So in project table i can have some rows with certification_date as null and some with a date (ex. 2020-06-18 00:12:07)

Now I have a search query with name and has a filter of startDate and endDate to filter certification_date. below is the Repository query with full signature.

@Query(value = "select distinct p.id, p.name, p.certification_date from project p WHERE (LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') and ((cast(:startDate as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) AND (cast(:endDate as timestamp) is null or p.CERTIFICATION_DATE < :endDate ))",  nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

So when searchProjects() executes with all the values, it returns the results correctly. But when i need no date filters, i.e startDate and endDate is null, it fails. (here we dont have any issue with searchKey because if searchKey is null I pass "" so it returns all values)

The Exception is

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone

So what do i need to do to make the startDate and endDate optional, so that if those dates are not passed just return all(whether certification_date is null or has some values) rows ?

I have also tried many solutions from Stackoverflow but they don't work for me.

spring-data-jpa spring-boot jpa postgresql spring
3 Answers
  1. The value of 'null' cannot be distinguished by JPA, especially for the mapping of 'date, timestamp, timestamptz' types in PostgreSQL.
  2. My solusion as follows:
    @Query(value = "select distinct p.id, p.name, p.certification_date from project p " +
            "where LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') " +
            "and (cast(cast(:startDate as text) as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) " +
            "and (cast(cast(:endDate as text) as timestamp) is null or p.CERTIFICATION_DATE < :endDate)",  nativeQuery = true)
    public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);
user_146228710 
class Employee {
 int id;
 Date birthDate;
}

List<Employee> findByBirthDateNotNullAndBirthDateLessThanEqualAndBirthDateGreaterThanEqual(Date startDate,Date endDate)
user_51569990

Add casting only when comparing to null and not when actual data comparison occurs.

@Query(value = "select distinct p.id, p.name, p.certification_date from project p WHERE (LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') and ((cast(:startDate as date) is null or p.CERTIFICATION_DATE >= :startDate) AND (cast(:endDate as date) is null or p.CERTIFICATION_DATE < :endDate ))",  nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);
user_41979950
Related