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

Iterative recurrence… Iteration method

algorithm methods recursion iteration

I have tried to solve the following recurrence relation using Iteration method,

C++ Time complexity in Oh notation of recursive function with for loop

recursion time complexity-theory

What would the time complexity for the following function in Oh-Notation?

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.