CODEWITHSUNDEEP
javamysqlhibernate
Linor
Linor

Reputation: 1860

Compare time part of datetime field in Hibernate

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?

Upvotes: 5

Views: 5747

Answers (2)

anon
anon

Reputation:

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

Upvotes: 1

Sietse
Sietse

Reputation: 7955

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

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

Upvotes: 4

Related Questions