[eclipselink-users] Recomended method for group-by time values?

[Phillip Ross <phillip.ross@xxxxxxxxx>]

Hi all...

I'm wondering if anyone has any recommendations/best practices for executing queries that use date/time values in the group-by clause.  I'm looking to to implement some reports using different aggregate functions using the timestamps as the groupings.  But, the date/time values are timestamps in the database and in SQL there would be some aggregate function i'd use on the timestamp fields to specify the grouping level in the group-by clause.

I dont immediately see any date-specific functions in the standard JPA query language that would be helpful to extract a year/month/week/day/hour/minute/second component from the timestamp values for grouping.

Anyone have a recommendation for doing this?  The final result would be something like aggregating records such as records-per-hour/day/month/year using the transformed timetamp value from the DB.  I'm also wanting cachable results, so the query would be run against the DB until the expiration... but I understand there are limitations on the cachability of the queries depending on what functions they may use, etc.

Anything available in JPA?  EclipseLink expression API?  Do/Should I use native queries and use DB-specific SQL?

Thanks in advance for any advice!
- Phillip