Compare time part of datetime field in Hibernate

Question

I've got an application that uses a hibernate(annotations)/mysql combination for ORM. In that application, I got an entity with a Date field. I'm looking for a way to select on that date within a time range (so hh:mm:ss without the date part).

In MySQL there's a function TIME(expression) that can extract the time part and use that in the where clause, but that does not seem to be available in Hibernate without switching to native queries. Is there an option in hibernate to do this, or should I loop through the results in java and do the comparison there? Would this be much slower as the MySQL solution, since that would not use indexes anyway?

Answer 1

The following functions are available in HQL, maybe you could use them:

second(...), minute(...), hour(...), day(...), month(...), year(...)

Answer 2

Add the expression as a SQL restriction rather than having a full native query. I don't know MySQL specifically, but imagine something like this:

Criteria criteria = session.createCriteria(MyTable.class);
criteria.add( 
  Expression.sql(
    "TIME( {alias}.my_date, 'hh:mm:ss') >= :1", 
    dateRangeMin, 
    new StringType()
  )
);