CODEWITHSUNDEEP
javasqlhibernatejpa
Dénes
Dénes

Reputation: 113

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

Upvotes: 1

Views: 2344

Answers (2)

Dénes
Dénes

Reputation: 113

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

Upvotes: 0

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

You can apparently use (some, at least) SQL Functions in Hibernate/HQL. This includes CAST, and requires that the DB respond appropriately (ie, TIME type exists and is castable from TIMESTAMP). If it works, it will probably work about like this:

Query q = em.createQuery( "SELECT t  FROM Foo t WHERE CAST(t.std as TIME) >= :p_1 ");

(please note I don't have a backing db/Hibernate instance to test this with. May depend on version of Hibernate too.)

Note that this will ignore any indices! This is more a function of the fact that you're only querying on part of the data, rather than some deficiency on Hibernate's part

Upvotes: 1

Related Questions