Spring Boot DataTable TIMESTAMP range search

[wallac3gg]

Timestamp FIELD - "created" ( Postgresql )

In postgresql I have dates in format

createdDate : "2018-11-01 23:51:33.534" (TIMESTAMP WITHOUT TIME ZONE)

I would like to retrieve all orders for 2018-11-01.

Spring DATA JPA - DATATABLE METHOD

@Override
public Predicate toPredicate(Root<Parlamentares> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {

  Expression<Timestamp> createdDate = root.get("created").as(Timestamp.class);

  if (dateBegi != null && dateEnd != null) {
    return criteriaBuilder.between(createdDate, dateBegi, dateEnd);
  } else if (dateBegi != null) {
    return criteriaBuilder.greaterThanOrEqualTo(createdDate, dateBegi);
  } else if (dateEnd != null) {
    return criteriaBuilder.lessThanOrEqualTo(createdDate, dateEnd);
  } else {
    return criteriaBuilder.conjunction();
  }
}

Everything works fine when i'm using "createdDate DATE column".

dateBegi : "2018-11-01" comes from daterangepick.

How to resolve this problem? Any ideia ?

Edit: code block

[darrachequesne]

What are the Java types of the dateBegi and dateEnd variables? java.sql.Date ? java.util.Timestamp?

Are you able to reproduce the issue, by forking the test here for example?

You could also set the log level of org.hibernate.SQL to DEBUG, to see the generated query.

[wallac3gg]

Both comes as STRING from (DataTablesInput input), then i convert to java.util.Date.

dateBegi and dateEnd both variables java.util.Date. I tried putting both variables java.sql.Date and it not worked.

Tried Expression with java.util.Date, without success.

SQL DEBUG :

================== 2018-11-26 : DEBUG 3340 --- [nio-8080-exec-6] org.hibernate.SQL : select parlamenta0_.id as id15, parlamenta0_.age as age25, parlamenta0_.created as created35, parlamenta0_.nascimento as nascimen45, parlamenta0_.nome as nome55, parlamenta0_.salary as salary65 from parlamentares parlamenta0 where (lower(cast(parlamenta0.created as varchar(255))) like ? escape ?) and cast(parlamenta0_.created as timestamp)>=? limit ?

========================

[darrachequesne]

I think you have to reset the value of the createdAt in the DataTablesInput object, so that the default filter (in your debug log lower(cast(parlamenta0_.created as varchar(255))) like ? escape ?) is not added to the query:

ColumnParameter parameter0 = input.getColumns().get(0);
Specification additionalSpecification = getAdditionalSpecification(parameter0.getSearch().getValue());
parameter0.getSearch().setValue(""); // <-- this line
return userRepository.findAll(input, additionalSpecification);

[wallac3gg]

Finally, that's the point, you are correct :

Solution for POSTGRESQL TIMESTAMP DATE RANGE, just reset the value :

public DataTablesOutput listParl(@Valid DataTablesInput input) {

    Column parameter0 = input.getColumns().get(6);
    Specification<Parlamentares> additionalSpecification  = new DateSpecification(parameter0.getSearch().getValue());
    parameter0.getSearch().setValue(""); 
    return repository.findAll(input, additionalSpecification );
}

But why i have to reset the value with a database TIMESTAMP column ? database DATE column works fine without reset. Anyway thank you my friend. /CLOSED

[darrachequesne]

You're welcome. :+1: