query to return a day's records from datetime field

Question

In a spring mvc application using hibernate and jpa, I have a query that needs to return all the records whose date falls within a specified day. The day comes ultimately from a url parameter in yyyy-mm-dd format and is stored in a joda localdate object. The entity whose records are searched by day has joda DateTime as the type for the property being searched. The underlying MySQL database has the date information stored in a TimeStamp field. In order to convert from localdate to datetime, I read the localdate api and tried day.toDateTimeAtStartOfDay(), but this narrows the parameter too far. I need to get all records within the 24 hour period of the day, not just those in the first second of the day. How can I structure the query so that it returns every record from the TimeStamp field with a specified day, including all times within that specified day?

Here is the method containing the query in the jpa repository:

@Override
public Collection<Encounter> findByDay(LocalDate day){
    System.out.println("@@@@@@@@@@@@@@@ made it into findByDay()");
    DateTime myDT = day.toDateTimeAtStartOfDay();
    Query query = this.em.createQuery("SELECT DISTINCT encounter FROM Encounter encounter WHERE encounter.dateTime LIKE :day");
    query.setParameter("day", myDT);
    return query.getResultList();       
}

Here is the sql for the underlying datatable in MySQL:

CREATE TABLE IF NOT EXISTS encounters(
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  patient_id int(11) UNSIGNED NOT NULL,
  office_id int(11) UNSIGNED NOT NULL,
  provider_id int(11) UNSIGNED NOT NULL,
  start_date TIMESTAMP,
  status varchar(50),
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (office_id) REFERENCES facilityAddresses(id),
  FOREIGN KEY (provider_id) REFERENCES providers(id)
)engine=InnoDB;

And here is the relevant part of the Encounter entity:

@Entity
@Table(name = "encounters")
public class Encounter extends BaseEntity{

    @Column(name="start_date")
    private DateTime dateTime;

    //other mappings of columns to properties

    // getters and setters

}  

---

EDIT:

Here is what I am testing based on Affe's suggestion:

@Override
public Collection<Encounter> findByDay(LocalDate day){
    System.out.println("kkkkkkkkkkkkkkkkkkkkkk inside repository.findByDay()  ");
    DateTime startDay = day.toDateTimeAtStartOfDay();
    DateTime startNextDay = startDay.plusDays(1);
    System.out.println("startDay is: "+startDay);
    System.out.println("startNextDay is: "+startNextDay);
    Query query = this.em.createQuery(
        "SELECT DISTINCT encounter " +
        "FROM Encounter encounter " +
        "WHERE encounter.dateTime >= :startDay and encounter.dateTime < :startNextDay");
    query.setParameter("startDay", startDay);
    query.setParameter("startNextDay", startNextDay);
    Collection<Encounter> myresult = query.getResultList();
    Object[] something = myresult.toArray();
    System.out.println("vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv num results is: "+myresult.size());
    for(int i=0;i<something.length;i++){
        System.out.println("nnnnnnnnnnnnnnnnnnnnnnnnn "+something[i].toString());
    }
    return myresult;
}

Here is the sql that inserts test data into the database:

INSERT IGNORE INTO encounters VALUES (1, 5, 2, 1, '2013-11-30 09:00:00','Active');
INSERT IGNORE INTO encounters VALUES (2, 4, 1, 2, '2013-12-15 10:00:00','Inactive');
INSERT IGNORE INTO encounters VALUES (3, 3, 4, 3, '2014-1-10 11:00:00','Active');
INSERT IGNORE INTO encounters VALUES (4, 2, 3, 4, '2014-1-13 12:00:00','Inactive');
INSERT IGNORE INTO encounters VALUES (5, 1, 1, 3, '2014-1-12 13:00:00','Inactive');

When I select 2014-1-10, 2014-1-13, 2014-1-12 or the other inserted dates as parameters, the system.out.println() above prints out zero for the number of results, so the for loop never runs. The query should return a result for each of those days.

For example, when I select 2014-1-10, the following is output in the eclipse console:

kkkkkkkkkkkkkkkkkkkkkk inside repository.findByDay()  
startDay is: 2014-01-10T00:00:00.000-08:00
startNextDay is: 2014-01-11T00:00:00.000-08:00
Hibernate: select distinct encounter0_.id as id1_10_, encounter0_.start_date as start2_10_, encounter0_.office_id as office4_10_, encounter0_.patient_id as patient5_10_, encounter0_.status as status3_10_ from encounters encounter0_ where encounter0_.start_date>=? and encounter0_.start_date<?
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv num results is: 0
number of encounters is: 0
aaaa hours of day, mins of hour for block: 8, 0
aaaa hours of day, mins of hour for block: 9, 0
aaaa hours of day, mins of hour for block: 10, 0
aaaa hours of day, mins of hour for block: 11, 0
aaaa hours of day, mins of hour for block: 12, 0
aaaa hours of day, mins of hour for block: 13, 0
aaaa hours of day, mins of hour for block: 14, 0
aaaa hours of day, mins of hour for block: 15, 0
aaaa hours of day, mins of hour for block: 16, 0

Does the format of the datetime output by System.out.println() have anything to do with the problem? What am I doing wrong?

Answer 1

The solution to the problem was very simple. I just needed to add @Type annotation to the Encounter entity's dateTime field as follows:

@Column(name="start_date")
@Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime dateTime;

My original jpa/sql code works fine after adding this one line of code to the entity.

Answer 2

I thing the problem is in date formats

You store date in your database in yyyy-MM-dd HH:mm:ss (i.e 2013-11-30 09:00:00) format and you are passig date in yyyy-MM-dd'T'HH:mm:ss (i.e 2014-01-10T00:00:00.000-08:00) format to query

first you have to convert your LocalDate in given format as

LocalDate localDate = new LocalDate(2010, 9, 14);
DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
String startDay = formatter.print(localDate);

then you have to set it to query parameter

Query query = this.em.createQuery(
    "SELECT DISTINCT encounter " +
    "FROM Encounter encounter " +
    "WHERE encounter.dateTime = "+ startDay +"");

hope this will solve your problem !

Answer 3

You need to search over the range of all timestamps that fall on the particular day. Functionality for treating a timestamp as a date within the database is DB specific and not available through the JPA API. (and often a bad idea anyway unless you've built a functional index based on the date values.)

@Override
public Collection<Encounter> findByDay(LocalDate day){
    DateTime startDay = day.toDateTimeAtStartOfDay();
    DateTime startNextDay = startDay.plusDays(1);
    Query query = this.em.createQuery(
        "SELECT DISTINCT encounter " +
        "FROM Encounter encounter " +
        "WHERE encounter.dateTime >= :startDay and encounter.dateTime < :startNextDay");
    query.setParameter("startDay", startDay);
    query.setParameter("startNextDay", startNextDay);
    return query.getResultList();       
}