How to query LocalDateTime with LocalDate?

Question

I've got a class which contains an atttribute of java.time.LocalDateTime type.

public class MyClass{
    // ...
    private LocalDateTime fecha;
    // ...
}

I'm using Spring Data repositories. What I want to accomplish is to query entities according to a date:

@Service
public interface IRepository extends CrudRepository<MyClass, UUID> {
    // ...
    public void deleteByFecha(LocalDate fecha);
    // ...
}

But this does not work, as an exception is thrown:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [2016-10-05] did not match expected type [java.time.LocalDateTime (n/a)];

So the question is how can I query MyClass in database by fecha but with a LocalDate?

EDIT Just in case somebody faces the same issue, I've come up with one solution: modify the Repository's method so that it looks as follows:

import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
// ...

@Service
public interface IRepository extends CrudRepository<MyClass, UUID> {

    @Transactional
    @Modifying
    @Query("DELETE FROM MyClass mtc WHERE YEAR(mtc.fecha)=?1 AND MONTH(mtc.fecha)=?2 AND DAY(mtc.fecha)=?3")
    public void deleteByFecha(Integer year, Integer month, Integer day);

}

score 9 · Accepted Answer · answered May 15 '17 at 14:02

9

Try this (not tested):

public interface IRepository extends CrudRepository<MyClass, UUID> {
    // ...
    default void delByFecha(LocalDate fecha) {

        deleteByFechaBetween(fecha.atStartOfDay(), fecha.plusDays(1).atStartOfDay());

    }

    void deleteByFechaBetween(LocalDateTime from, LocalDateTime to);
    // ...
}

answered May 15 '17 at 14:02

Cepr0

28,144
8
75
101

Another possible solution I didn't think about... Thanks – joninx May 16 '17 at 06:23
It worked for me. Can I ask why default is needed in this case? – funder7 May 18 '20 at 11:25
Thank you, you saved me some time :) – funder7 Sep 10 '20 at 12:29

dardo · Answer 2 · 2019-08-16T15:20:27.320

Zombie Thread, but thought I'd throw my solution into the ring.

I had a similar issue, I ended up using a Hibernate @Formula

For example:

class MyClass {

  private LocalDateTime fecha;

  /* fecha referenced within the annotation should be the column name. 
   * So, if it's different the Java field name (i.e. fecha_dtm or something), 
   * make sure to use it.
  */
  @Formula("CAST(fecha as DATE)") 
  private LocalDate fechaDate;

}

Then your repository:

public interface IRepository extends CrudRepository<MyClass, UUID> {
  deleteByFechaDate(LocalDate fecha); // note it's FechaDate, not Fetcha
}

Try to stick to ANSI SQL compliant functions (CAST is SQL-92 compliant, so pretty widely accepted) to keep things consistent across database implementations. However, DB specific functions can be used, you'll just lose portability.

Hopefully this helps you!

The CAST on pl/sql doesn't work as expected, it still returns a datetime. I suggest using TRUNC — Bruno Mendes, Sep 03 '20 at 15:53

score 2 · Answer 3 · answered May 25 '20 at 08:02

I had a similar question today (I was making SELECT, not a DELETE) and after posting it on here, I came up with two solutions:

@Query(value = "DELETE FROM MyClass mc WHERE DATE(fecha) =:fecha", nativeQuery = true)
public void deleteByFecha(LocalDate fecha);

Alternatively, following Cepr0's answer, I tested with success his solution:

default void deleteByFecha(LocalDate fecha) {
    deleteByFechaBetween(fecha.atStartOfDay(), fecha.plusDays(1).atStartOfDay());

}

void deleteByFechaBetween(LocalDateTime from, LocalDateTime to);

score 1 · Answer 4 · answered Apr 23 '18 at 18:46

1

I know that this one is a old issue, but for the future, who is with the same problem can use this solution:

 default void delByFecha(@RequestParam(name = "date") @DateTimeFormat(iso = ISO.DATE) LocalDate date)

It's works perfectly!

answered Apr 23 '18 at 18:46

All Pereira

139
1
2
13

How to query LocalDateTime with LocalDate?

4 Answers4

Linked