Interval hibernation group

Question

I have a table with a timestamp field of type datetime. I need to aggregate data between a specific start and end time into groups x, representing time intervals of equal length, where x is provided as a function parameter.

What would be the best way to do this with Hibernate?

EDIT: some explanation

Mysql table:

data_ts: datetime pk value1 : int value2 : bigint ... 

Entity Class:

 Calendar dataTs; Integer value1; BigDecimal value2; ... 

I am looking for an HQL query that does something like

 select max(c.value1), avg(c.value2) from MyClass c where c.dataTs between :start and :end group by <interval> 

where the entire time period is grouped into x time intervals with the same size.

Example:

 Start : 2008-10-01 00:00:00 End : 2008-10-03 00:00:00 (2 days) Groups: 32 

must be grouped at an interval of 1.5 hours (48 hours / 32):

 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 ... 

Answer 1

I tried to solve the same problem. I have to group the data at a 2-hour interval for one day. In fact, "clean" sleep mode should not be used this way. So I added the original version of SQL for the Hibernate criteria. For your case, it may look like this (I use MySQL syntax and functions):

 int hours = 2; // 2-hours interval Criteria criteria = session.createCriteria( MyClass.class ) .add( Restrictions.ge( "dataTs", start ) ) .add( Restrictions.le( "dataTs", end ) ); ProjectionList projList = Projections.projectionList(); projList.add( Projections.max( "value1" ) ); projList.add( Projections.avg( "value2" ) ); projList.add( Projections.sqlGroupProjection( String.format( "DATE_ADD( DATE( %s_.dataTs ), INTERVAL( HOUR( %s_.dataTs ) - HOUR( %s_.dataTs) %% %d ) HOUR) as hourly", criteria.getAlias(), criteria.getAlias(), criteria.getAlias(), hours ), String.format( "DATE_ADD( DATE( %s_.dataTs ), INTERVAL( HOUR( %s_.dataTs) - HOUR( %s_.dataTs ) %% %d ) HOUR)", criteria.getAlias(), criteria.getAlias(), criteria.getAlias(), hours ), new String[]{ "hourly" }, new Type[]{ Hibernate.TIMESTAMP } ) ); criteria.setProjection( projList ); List results = criteria .setCacheable( false ) .list(); 

The code looks a little ugly, but it solves the problem. I hope that the general idea will be useful to you.

Answer 2

Hibernation is intended for comparison between the graph of objects (entities and value types) and their presentation in a relational database.

From your description of the question, you really do not mention any objects that you are modeling, so I would suggest abandoning your own SQL query.

http://www.hibernate.org/hib_docs/reference/en/html/querysql.html

Perhaps if you placed the table structure, this may provide more context for your question?