Databases
StackOverflow2013
Postsdbo
POFilter for TIME part of TIMESTAMP doesn't work (JPA2, HIBERNATE)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFilter for TIME part of TIMESTAMP doesn't work (JPA2, HIBERNATE)
    text
    copied!<p>I would like to filter by the time part of a timestamp. I use <strong>JPA2</strong> with <strong>hibernate</strong> and <strong>oracle</strong> database. I have to write a JPA query, which returns entities, which "<em>std</em>" 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.</p> <p>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.</p> <p>My query looks like this:</p> <pre><code>Query q = em.createQuery( "SELECT t FROM Foo t WHERE t.std &gt;= :p_1 "); q.setParameter("p_1", myDate, TemporalType.TIME); </code></pre> <p>where my date is: 2013-12-11T12:01:01 (if I use toString: Wed Dec 11 12:01:01 UTC 2013 )</p> <p>In Entity class, the <em>std</em> property looks like this:</p> <pre><code>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; } </code></pre> <p>I also tried to change the annotation of the propertys in the entityClass to this:</p> <pre><code>@javax.persistence.Temporal(javax.persistence.TemporalType.TIME) public java.util.Date getStd() { return std; } </code></pre> <p>but it didn't help.</p> <p>In sql, the std property is TIMESTAMP:</p> <pre><code>"STD" TIMESTAMP (6) NOT NULL ENABLE, </code></pre> <p>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?</p> <p>Thanks!</p> <p>Edit:</p> <p>For example If the std values are the following in database:</p> <pre><code>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 </code></pre> <p>And I put <strong>"2013-12-11T12:00:00"</strong> in the query, I would like to get back all entities, which "<em>std</em>" has higher TIME, regardless from the date. In this example, these:</p> <pre><code>2013-12-08T14:00:00 2013-12-11T14:00:00 2013-12-14T14:00:00 </code></pre> <p>If I check hibernate debug log, I can see the std is mapped to org.hibernate.TimeType.</p> <blockquote> <p>[org.hibernate.hql.internal.ast.tree.DotNode] (http-/127.0.0.1:8080-1) getDataType() : std -> org.hibernate.type.TimeType@f600d7a</p> <p>[org.hibernate.hql.internal.ast.tree.FromReferenceNode] (http-/127.0.0.1:8080-1) Resolved : t.std -> foo0_.STD</p> <p>[org.hibernate.hql.internal.ast.tree.FromReferenceNode] (http-/127.0.0.1:8080-1) Resolved : t -> foo0_.ID</p> </blockquote>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload