Perform a date search in a timestamp column using spring data

Question

I am trying to perform a search only for the current date in a column timestamp, or without giving the time and yes only the date.

Model:

@DateTimeFormat(pattern = "dd/MM/yyyy hh:mm")
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "data")
private Date date;

I currently use Spring Data to perform the query.

Service class:

public List<Order> findByDate(){
    return orderRepository.findByDate(new Date());
}

Spring Data repository:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByDate(Date date); 
}

The problem that the query is made taking into account the current timestamp with minute and second hour values and I want the select is made only by selecting the current date records.

Select with example Binding:

SELECT 
    order0_.id AS id1_1_,
    order0_.data AS data2_1_,
    order0_.pedido AS pedido3_1_,
    order0_.pagamento AS pagament4_1_,
    order0_.telefone AS telefone5_1_,
    order0_.status AS status6_1_,
    order0_.total AS total7_1_,
    order0_.usuario AS usuario8_1_
FROM
    spring_admin.pedidos_zuhause order0_
WHERE
    order0_.data = ?;

Binding Parameter [1] as [TIMESTAMP] - [Wed Sep 20 10:15:35 BRT 2017]

I know I saw Mysql I can do this using the function date(), but how can I do it for Hibernate?

Answer 1

Since the database column also stores information that not only day/month/year you have to make the generated query consider this.

One way to do this is by shopping with date between the start and end of the day. The query in your repository would look like this:

List<Order> findByDateBetween(final Date start, final Date end);

The call on your service would be something like this:

public List<Order> findByDate(){
    final Date start = // recupera o início do dia;
    final Date end = // recupera o fim do dia;
    return orderRepository.findByDate(start, end);
}

Here are ways to get start and end dates: How to obtain the start time and end time of a day?

As you yourself mentioned native DBMS functions can be used. Spring Data provides other means for creating queries, such as @Query and QueryDSL, in addition to the already supported by JPA by default.