Find entities between dates in HQL

I'm using HQL and javax.persistence. In my MySQL database, I have a datetime (e.g. 2018-01-22 18:00:00). From the client-side, I'm being passed a date with no time (e.g. 2018-01-20). I want to find all the entities where the datetime is between a startDate and an endDate.

public List<BillingRunEntity> getBillingRuns(List<String> accountIds, LocalDate startDate, LocalDate endDate) {
    String query = "SELECT DISTINCT bre " +
               "FROM BillingRunEntity bre " +
               "WHERE bre.accountId in :accountIds " +
               "AND bre.billingDateTime BETWEEN :startDate AND :endDate";

    return entityManager
        .createQuery(query, BillingRunEntity.class)
        .setParameter("accountIds", accountIds)
        .setParameter("startDate", startDate)
        .setParameter("endDate", endDate)
        .getResultList();
}

And my BillingRunEntity.java's billingDateTime field:

@Column(name = "billing_date_time")
private ZonedDateTime billingDateTime;

1) Trying to run this query results in the following error. How do I resolve this, considering I don't care about the time?

java.lang.IllegalArgumentException: Parameter value [2018-07-03] did not match expected type [java.time.ZonedDateTime (n/a)]
    at org.hibernate.jpa.spi.BaseQueryImpl.validateBinding(BaseQueryImpl.java:874)
    at org.hibernate.jpa.internal.QueryImpl.access$000(QueryImpl.java:80)
    at org.hibernate.jpa.internal.QueryImpl$ParameterRegistrationImpl.bindValue(QueryImpl.java:248)
    at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:620)
    at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:180)
    at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:49)

2) Will this query work the way I expect? I don't care about times here - if the database has 2018-01-22 18:00:00, and I pass a startDate of 2018-01-22 and an end date of 2018-01-23 (or even 2018-01-22), I'd expect to pull that record.

Solution

Assuming your ZonedDateTime in the database is always stored in UTC you can simply convert to LocalDate

ZoneId utc = ZoneId.of("UTC");
ZonedDateTime startTime = startDate.atStartOfDay(utc);
ZonedDateTime endTime = endDate.atStartOfDay(utc).plusDays(1).minusNanos(1);

[...]

    .setParameter("startDate", startTime)
    .setParameter("endDate", endTime)

minusNanos(1) might be an overkill but the BETWEEN operator is inclusive on both ends.

If you don't use the same time zone for all the values in the database you might have to dig into the billing_date_time column type used by your table to understand how it handles the time zone information.