HQL(hibernate) timestamp range match

Question

I need to write a query to get an object between a range of time, currently the query looks like this:

Timestamp from = ... 
Timestamp to = ...

getHibernateTemplate().find("from " + Person.class.getName() + " ml where ml.lastModifiedOn>="+from.toString()+" and m1.lastModifiedOn<=" + to.toString());

However, this doesnot work for obvious reasons. How can I format the timestamp to be acceptable by the query.

org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: 16 near line 1, column 123 [from Person ml where ml.lastModifiedOn>=2010-02-12 16:00:21.292 and m1.lastModifiedOn

Answer 1

You can simply pass a long (from.getTime()) in the comparison, if it is represented as long in the DB.

Otherwise you can use these functiomns: second(...), minute(...), hour(...), day(...), month(...), and year(...)

Answer 2

How about something like this?

    String sql = "from " + Person.class.getName() + " ml where ml.lastModifiedOn>= ? and m1.lastModifiedOn<= ?";
    Date from = ...;
    Date to = ...;

    getHibernateTemplate().find(sql, new Object[] {from,to});

Answer 3

You're missing single quotes in your current query. The following should work:

from Person ml where ml.lastModifiedOn 
between '2010-02-12 16:00:21.292' and '2010-02-12 23:00:21.292' 

Note that I don't know why you're not passing Date instances to the following query:

from Person ml where ml.lastModifiedOn between :from and :to 

Are you using java.sql.Timestamp here? If yes, you shouldn't.

Answer 4

If you want to query for something between you can do the following:

 public List findPerson() {
   Date from = ...;
   Date to = ...;
   return entityManager.createQuery(
     "SELECT p from Person p WHERE p.lastModifiedOn BETWEEN ?1 AND ?2")
     .setParameter(1,from, TemporalType.DATE)
     .setParameter(2,to, TemporalType.DATE).getResultList();
}

You might need to change TemporalType.DATE to whatever you are using