JPA-QL query find all entities with LocalDateTime timestamp between LocalDate startDate and LocalDate endDate

Question

I have a JPA entity TimeSlot with a LocalDateTime field called startDateTime:

@Entity
public class TimeSlot {

    private LocalDateTime startDateTime;
    ...
}

I am using Hibernate on WildFly 10.1. How do I query all entities with the startDateTime between startDate and endDate?

private List<TimeSlot> getTimeSlotsByStartDateEndDate(LocalDate startDate, LocalDate endDate) {
    return entityManager.createNamedQuery("TimeSlot.findByStartDateEndDate", TimeSlot.class)
            .setParameter("startDate", startDate)
            .setParameter("endDate", endDate).getResultList());
}

This query fails because a timestamp is not a date:

@NamedQueries({
        @NamedQuery(name = "TimeSlot.findByStartDateEndDate",
                query = "select t from TimeSlot t" +
                        // fails because a timestamp is not a date
                        " where t.startDateTime between :startDate and :endDate"),
})

It seems at least some DBs do not like to mix DATEs and DATETIMEs/TIMESTAMPs in the `BETWEEN` operator. You are passing `LocalDate`s (`startDate` and `endDate`), which Hibernate obviously casts to DATEs, while `t.startDateTime` is a TIMESTAMP. I can think of 2 solutions (but cannot verify at the moment): (1) convert the `LocalDate` into `LocalDateTime` in Java or (2) use JPA's `FUNCTION` and convert the DATEs into TIMESTAMPs in the DB, using the database-specific function for your underlying DB. I would go with (1). Good luck :) — Nikos Paraskevopoulos, May 31 '17 at 19:00
1) means knowing what the first and last timestamp of a day is. Leap seconds make this less fun. I 'll investigate 2) first. — Geoffrey De Smet, May 31 '17 at 19:17

score 0 · Answer 1 · answered Oct 03 '20 at 17:50

You must convert LocalDateTime and LocalDate to java.sql.Timestamp then add your converter classes to the persistent.xml file then everything must be ok. For LocalDateTimeConverter :

import java.time.LocalDateTime;
import java.sql.Timestamp;
 
@Converter(autoApply = true)
public class LocalDateTimeAttributeConverter implements AttributeConverter<LocalDateTime, Timestamp> {
     
    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime locDateTime) {
        return locDateTime == null ? null : Timestamp.valueOf(locDateTime);
    }
 
    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
        return sqlTimestamp == null ? null : sqlTimestamp.toLocalDateTime();
    }
}

For LocalDateTime:

import java.sql.Date;
import java.time.LocalDate;
 
@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {
     
    @Override
    public Date convertToDatabaseColumn(LocalDate locDate) {
        return locDate == null ? null : Date.valueOf(locDate);
    }
 
    @Override
    public LocalDate convertToEntityAttribute(Date sqlDate) {
        return sqlDate == null ? null : sqlDate.toLocalDate();
    }
}

Lastly, add your classes to the persistent.xml

<class>xxxx.model.Entities</class>
<class>xxxx.converter.LocalDateConverter</class>
<class>xxxx.converter.LocalDateTimeConverter</class>

JPA-QL query find all entities with LocalDateTime timestamp between LocalDate startDate and LocalDate endDate

1 Answers1