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

Pascal Thivent · Accepted Answer · 2010-05-19T16:20:35.080

4

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.

edited May 19 '10 at 16:20

answered May 19 '10 at 14:19

Pascal Thivent

562,542
136
1,062
1,124

Yes, I am using Timestamp basically throughout the project, and though this works: Timestamp from =..; Timestamp to = ..; DetachedCriteria criteria = DetachedCriteria.forClass(Person.class); criteria.add(Restrictions.between("lastModifiedOn", from, to)); List results = getHibernateTemplate().findByCriteria(criteria); – Saky Jul 01 '10 at 14:18

score 2 · Answer 2 · answered May 19 '10 at 11:38

2

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(...)

answered May 19 '10 at 11:38

Bozho

588,226
146
1,060
1,140

1

In the DB it is represented as DATETIME and not as long. How would I use those functions for a whole datetime match ? – Saky May 19 '10 at 11:42

score 1 · Answer 3 · answered May 19 '10 at 13:56

1

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});

answered May 19 '10 at 13:56

limc

39,366
20
100
145

I tried that and I get an error. [ERROR] 13:58:03 PARSER - Invalid path: 'm1.lastModifiedOn' [ERROR] 13:58:03 PARSER - :0:0: unexpected end of subtree [ERROR] 13:58:03 PARSER - left-hand operand of a binary operator was null org.springframework.orm.hibernate3.HibernateQueryException: Invalid path: 'm1.lastModifiedOn' [from it.Person ml where ml.lastModifiedOn>=? and m1.lastModifiedOn<=?]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'm1.lastModifiedOn' [from it.Person ml where ml.lastModifiedOn>=? and m1.lastModifiedOn<=?] ... strange! – Saky Jun 04 '10 at 13:29

score 0 · Answer 4 · answered May 19 '10 at 14:31

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

HQL(hibernate) timestamp range match

4 Answers4

Linked