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?

Gravois

If my answer was helpful and works, please mark it as a solution. Otherwise tell me, so I'll look for another answer. – Fantastically

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.

Fantastically

This looks like java 7 dates? – Gravois

I see, you want to pass Java 8 date... Why can't you make an adapter in your service? – Fantastically

What do I need to adapt, java 8 -> java 7 date? That would be a bit silly no? – Gravois

No? In Java 7 you did all those walkarounds with Date and Calendar. Why not to adapt now. I'll search for a direct solution, but I would mind using adapter. The efficiency of the application doesn't rely on it. There is hibernate-java8, but I wouldn't use it yet. In hibernate 5.2 it's available by default – Fantastically

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)

Gravois