Dénes Dénes - 6 months ago 19
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)
@javax.persistence.Temporal(javax.persistence.TemporalType.TIMESTAMP)
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:

@javax.persistence.Temporal(javax.persistence.TemporalType.TIME)
public java.util.Date getStd()
{
return std;
}


but it didn't help.

In sql, the std property is TIMESTAMP:

"STD" TIMESTAMP (6) NOT NULL ENABLE,


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?

Thanks!

Edit:

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

2013-12-08T10:00:00
2013-12-08T14:00:00
2013-12-11T10:00:00
2013-12-11T14:00:00
2013-12-14T10:00:00
2013-12-14T14:00:00


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:

2013-12-08T14:00:00
2013-12-11T14:00:00
2013-12-14T14:00:00


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-/127.0.0.1:8080-1)
getDataType() : std -> org.hibernate.type.TimeType@f600d7a

[org.hibernate.hql.internal.ast.tree.FromReferenceNode]
(http-/127.0.0.1:8080-1) Resolved : t.std -> foo0_.STD

[org.hibernate.hql.internal.ast.tree.FromReferenceNode]
(http-/127.0.0.1:8080-1) Resolved : t -> foo0_.ID

Answer

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();
cal.setTime(myDate);

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