[FIXED] JPA - fetching by date using LocalDate or LocalDateTime

Issue

I'm building a basic hotel management system with Spring and JPA. I have an entity called Order which represents a single booking. It contains check in and check out dates as LocalDateTime:

private LocalDateTime checkin;
    
private LocalDateTime checkout;

I have a JPA repository OrderRepository linked to Order. All default methods (find, save, etc) work fine.

I have some test data in 'orders' table:

id room_id  checkin (DATETIME)          checkout (DATETIME) 
1     1     2020-06-08 00:00:00.000000  2020-06-09 00:00:00.000000
3     2     2020-06-09 00:00:00.000000  2020-06-19 00:00:00.000000
4     1     2020-06-09 00:00:00.000000  2020-06-19 00:00:00.000000

In my OrderRepository, I wanted to have a method to fetch all orders matching specific check in or check out dates. The problem is, I'm not able to make it work with '='. while it works fine with '<' or '>'.

For instance, having:

public interface OrderRepository extends JpaRepository<Order, Integer> {
    List<Order> findAllByCheckin(LocalDateTime checkin);
}

then

LocalDateTime date = LocalDateTime.of(2020, 6, 9, 0, 0);        
List<Order> _orders = orderRepository.findAllByCheckin(date);

should return 2 items, but returns an empty list.

Hibernate log output:

Hibernate: select [...] from orders order0_ where order0_.checkin=?
o.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [TIMESTAMP] - [2020-06-09T00:00]

When I run the exact same SQL manually against the DB, I get a correct result (2 rows):

select * from orders order0_ where order0_.checkin='2020-06-09T00:00'

id room_id  checkin (DATETIME)          checkout (DATETIME)     
3   2       2020-06-09 00:00:00.000000  2020-06-19 00:00:00.000000
4   1       2020-06-09 00:00:00.000000  2020-06-19 00:00:00.000000

I also tried defining the query manually using JPQL.

@Query(value = "select o from Order o where o.checkin = :checkin")
List<Order> findAllByCheckin(@Param("checkin") LocalDateTime checkin);

Hibernate log - identical with the one above:

Hibernate: select [...] from orders order0_ where order0_.checkin=?
o.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [TIMESTAMP] - [2020-06-09T00:00]

with the same result - empty list returned.

What's interesting, when I change the query replacing '=' with '<' or '>', it works fine:

@Query(value = "select o from Order o where o.checkin < :checkin")
List<Order> findAllByCheckin(@Param("checkin") LocalDateTime checkin);

returns 1 element, as expected.

What am I doing wrong?

I use:

• Spring Boot Starter JPA 2.2.6
• MySQL

Thanks for all suggestions.

Solution

I got the answer. MySQL stores all entries as UTC timezone and it looks like Hibernate converts the value of LocalDateTime from my local timezone to UTC before querying the DB. So, in my case LocalDateTime storing "2020-06-09 00:00:00.000000" became "2020-06-08 22:00:00.000000" in the query, what explains why '=' was not matching those records.

Maybe there is some configuration to control it, but it looks like this is how it works by default.

Answered By - Jakub Gąszczak