Query on Date only with Spring Boot Data JPA / Java 8 Instant?

Question

I have a Spring boot 1.4.x application that uses the starter jpa in combination with H2/Postgresql. I have an entity that stores a date as an Instant (with Instant.now(), based on Java 8: What's the difference between Instant and LocalDateTime? answer), and the associated table stores this field as a timestamp.

For the storing to work I had to setup a converter that converts Instant's to sql.Timestamps & vice versa, which seems to work (Timestamp.from(instant) and timestamp.toInstant())

My question is if there is a straightforward way to query by Date only using this instant using JPA repository, eg.

List<Orders> findBySaleTime(.... day)

Or am I forced to find a way to convert the day to two instants & do an in between query?

Answer 1

There are two approaches:

1st one:

List<Orders> findBySaleTimeBetween(DateTime start, DateTime end);

2nd one:

List<Orders> findBySaleTime(DateTime date);

It's worth trying to save dates rounded as much as possible, so if you only need day, set hours and minutes to certain values for all the entities.

Answer 2

public List<SaleOrder> findByDate(String date) {
    Instant startOfDay = Instant.parse(date).truncatedTo(ChronoUnit.DAYS);
    Instant endOfDay = startOfDay.plus(Duration.ofDays(1));

    return saleOrderRepository.findBySaleTimeAfterAndSaleTimeBefore(startOfDay, endOfDay);
}

Where the date is an UTC date passed from the client, seems to be the most straightforward to do this with java 8 dates. (Credits to Liste & Yawkat on freenode #java for help/pointers)