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?

If my answer was helpful and works, please mark it as a solution. Otherwise tell me, so I'll look for another answer. — xenteros, Sep 29 '16 at 06:30

score 3 · Answer 1 · answered Sep 28 '16 at 22:06

3

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.

answered Sep 28 '16 at 22:06

xenteros

15,586
12
56
91

This looks like java 7 dates? – Busata Sep 29 '16 at 06:46
I see, you want to pass Java 8 date... Why can't you make an adapter in your service? – xenteros Sep 29 '16 at 06:49
What do I need to adapt, java 8 -> java 7 date? That would be a bit silly no? – Busata Sep 29 '16 at 08:22
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 – xenteros Sep 29 '16 at 08:25

score 2 · Answer 2 · answered Sep 29 '16 at 10:18

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)

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

2 Answers2