Compare the temporary portion of the datetime field in Hibernate

Question

I have an application that uses a combination of Hibernate (annotations) / MySQL for ORM. In this application, I got an object with a Date field. I am looking for a way to select this date within a time range (therefore hh:mm:sswithout a date part).

MySQL has a function TIME(expression)that can extract some of the time and use it in the where clause, but apparently it is not available in Hibernate without switching to its own queries. Is there an option in hibernate to do this, or should I look at the results in java and do a comparison there? Will it be much slower than the MySQL solution, because in any case it will not use indexes?

Answer 1

The following functions are available in HQL, perhaps you can use them:

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

Answer 2

Add the expression as a SQL constraint, rather than a complete 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()
  )
);