Dénes Dénes - 1 year ago 49
Java Question

Filter for TIME part of TIMESTAMP doesn't work (JPA2, HIBERNATE)

I would like to filter by the time part of a timestamp.
I use JPA2 with hibernate and oracle database. I have to write a JPA query, which returns entities, which "std" properties's TIME part is higher than the parameter's TIME part.
I'm not able to change the database side, and I cannot use native query.

For example I would like to search all entities which std time is greater than 12:01. So I put this date parameter in my JPA query: 2013-11-11T12:01:01, but nothing happens, the result list contains elements with std parameter (11:00) 2013-12-13T11:00:00.

My query looks like this:

Query q = em.createQuery( "SELECT t FROM Foo t WHERE t.std >= :p_1 ");
q.setParameter("p_1", myDate, TemporalType.TIME);

where my date is: 2013-12-11T12:01:01
(if I use toString: Wed Dec 11 12:01:01 UTC 2013 )

In Entity class, the std property looks like this:

private java.util.Date std;

@javax.persistence.Column(name = "STD", nullable = false, insertable = true, updatable = true)
public java.util.Date getStd()
return std;

public void setStd(java.util.Date value)
this.std = value;

I also tried to change the annotation of the propertys in the entityClass to this:

public java.util.Date getStd()
return std;

but it didn't help.

In sql, the std property is TIMESTAMP:


I must use JPA query with hibernate, the native query is not an option.
Can someone explai why it doesn't work and how can I do it properly?



For example If the std values are the following in database:


And I put "2013-12-11T12:00:00" in the query, I would like to get back all entities, which "std" has higher TIME, regardless from the date. In this example, these:


If I check hibernate debug log, I can see the std is mapped to org.hibernate.TimeType.

[org.hibernate.hql.internal.ast.tree.DotNode] (http-/
getDataType() : std -> org.hibernate.type.TimeType@f600d7a

(http-/ Resolved : t.std -> foo0_.STD

(http-/ Resolved : t -> foo0_.ID

Answer Source

The solution is:

Query q = em.createQuery( 
    "SELECT t FROM Foo t WHERE ( EXTRACT(HOUR FROM t.std) * 60 + EXTRACT(MINUTE FROM t.std) ) >= :p_1" 

Calendar cal = Calendar.getInstance();

int minutes = cal.get(Calendar.HOUR_OF_DAY) * 60 + cal.get(Calendar.MINUTE);
q.setParameter("p_1", minutes);