Hibernate group by time interval

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.

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

EDIT: some explanations

mysql Table:

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

Entity class:

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

I am looking for a 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 whole time period is grouped into x equally sized time intervals.

Example:

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

would need to be grouped by a time 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
...

Answers


I've tried to solve the same problem. I have to group data by 2-hours interval within one day. In fact, "pure" Hibernate isn't supposed to be used this way. So I added native SQL projection to Hibernate's criteria. For your case it could look like this (I'm using MySQL syntax & 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 is looking a little bit ugly but it solves the problem. Hope the general idea will be helpful for you.


Hibernate is for mapping between a graph of objects (entities and value-types) and their representation in a relational database.

From your question description, you don't really mention any entities that you are modelling, so I would suggest dropping down into a native SQL query.

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

Perhaps if you posted the table structure, this may give more context for your question?


Need Your Help

Is it possible to programmatically detect corrupt Access 2007 database tables?

ms-access ms-access-2007

Is it possible via code to programmatically (from .NET for example via SQL query) to ask an Access database if it is corrupt or have tables with corrupt rows in it?

inline javascript are interrupting the load of the html

javascript jquery css dom

I want the html are fully loaded then execute the inline javascript because this script stops the load of the html document.