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.


Need Your Help

What is the basic architecture of a node.js + mongodb app?

mongodb node.js

I've been looking for ages with no luck. I found some modules on GitHub but unfortunately, were outdated. Does anyone know any CRUD module or tutorial for node.js 0.6.x + mongodb?

how to mark records that are read in mssql

c# asp.net sql-server tsql

I have created a system that sends some information by SMS daily to our customers. as the procedure of sending SMS can take a while this is done by multiple processes that each process sends a limi...

CSS Horizontal Menu : Text Align Bottom

css menu

well i am not good with CSS menus.... i need TO MAKE THIS but was unable to align text to the bottom of listli

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.