title: Hibernate group by time interval
body: <p>I have a table with a timestamp field of type datetime. I need to aggregate the data between a defined start and end time into x groups representing time intervals of equal length, where x is supplied as function parameter.</p>
  
  <p>What would be the best way to do this with Hibernate?</p>
  
  <p>EDIT: some explanations</p>
  
  <p>mysql Table:</p>
  
  <pre><code>data_ts: datetime pk
  value1 : int
  value2 : bigint
  ...
  </code></pre>
  
  <p>Entity class:</p>
  
  <pre><code>Calendar dataTs;
  Integer value1;
  BigDecimal value2;
  ...
  </code></pre>
  
  <p>I am looking for a HQL query that does something like</p>
  
  <pre><code>select max(c.value1), avg(c.value2) from MyClass c 
    where c.dataTs between :start and :end group by &lt;interval&gt;
  </code></pre>
  
  <p>where the whole time period is grouped into x equally sized time intervals.</p>
  
  <p>Example:  </p>
  
  <pre><code>Start : 2008-10-01 00:00:00   
  End   : 2008-10-03 00:00:00 (2 days)
  Groups: 32
  </code></pre>
  
  <p>would need to be grouped by a time interval of 1.5 hours (48 hours / 32):</p>
  
  <pre><code>2008-10-01 00:00:00 - 2008-10-01 01:29:59
  2008-10-01 01:30:00 - 2008-10-01 02:59:59
  2008-10-01 02:00:00 - 2008-10-01 04:29:59
  ...
  </code></pre>