Re: Re[eclipselink-users] comended method for group-by time values?

[James Sutherland <jamesssss@xxxxxxxxx>]

JPQL does not currently have any such date functions, but EclipseLink does
provider several such functions through its Expression criteria API.  So you
could define the query using EclipseLink Expressions.

To have the function support adding to EclipseLink JPQL see,
https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814

You could also use a native SQL query.

For object caching, since you are using group-by and aggregating data, you
will not be reading object, so not using the object cache.  You can use the
query-cache, the query-cache is enabled through Query hints in EclipseLink,
and any Query can use the query-cache, no matter if it using native SQL or
functions.



Phillip Ross-3 wrote:
> 
> 
> 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
> 
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/Recomended-method-for-group-by-time-values--tp23029063p23040071.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.