title: What is the best way to query a JPA entity for instances based on the current date+time?
body: <p>I have an entity type that has two dates, startDate and endDate:</p>
  
  <pre><code>import org.joda.time.DateTime;
  
  @Entity
  public class Thing {
      @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
      private DateTime startDate;
  
      @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
      private DateTime endtDate;
  }
  </code></pre>
  
  <p>I have a Spring Data-JPA repository (extends <code>CrudRepository</code>) for this entity type, and in there I need a way to query for all <code>Thing</code>s that are "active" where "active" is defined as</p>
  
  <p><em>startDate &lt;= now &lt;= endDate</em></p>
  
  <p>I tried a JPQL query like this, but it's not producing the expected results in some of our tests:</p>
  
  <pre><code>@Query("select t from Thing t where t.startDate &lt;= CURRENT_TIMESTAMP and t.endDate &gt;= CURRENT_TIMESTAMP")
  public Iterable&lt;Thing&gt; findAllActive();
  </code></pre>
  
  <hr>
  
  <p>I also tried a method-name-convention query, which does produce the results I expect, but it's awkward:</p>
  
  <pre><code>public Iterable&lt;Thing&gt; findByStartDateBeforeAndEndDateAfter(DateTime minStartDate, DateTime maxEndDate);
  </code></pre>
  
  <p>I can wrap that in my <code>@Service</code> like</p>
  
  <pre><code>public Iterable&lt;Thing&gt; getActiveThings() {
      DateTime now = DateTime.now();
      return repository.findByStartDateBeforeAndEndDateAfter(now, now);
  }
  </code></pre>
  
  <p>So the service interface is clean, but the repo interface is ugly and awkward (in my opinion).</p>
  
  <p>Is there a better way to construct this Repository query method using JPA, either a better method-name-based query or an explicit JPQL query? I'd prefer to not have to manually construct the Query object in code (right now I don't even have a concrete class for the Repository, only an interface), but if that's the best way I'll consider it.</p>