Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Criteria API and grouping by a truncated date
Criteria API and grouping by a truncated date [message #636667] Tue, 02 November 2010 12:14 Go to next message
Janne is currently offline JanneFriend
Messages: 10
Registered: February 2010
Junior Member
I'm using EclipseLink 2.1.1 and SQL Server 2008. I can't figure out how to group Criteria API query's results by the day part of a date.

I have an entity that's something like this:

class Event{

Long id;

Date created;


I'd like to create a Criteria API query that returns the amount of Event's for each day. As the "created" property is a TIMESTAMP, I'd have to truncate it in the query to get the daily event counts. In SQL this would be something like:

-- clip --

select trunc(created), count(*) from event group by trunc(date);

SQL Server 2008:

select DATEADD(dd, 0, DATEDIFF(dd, 0, created)), count(*)
from event
group by DATEADD(dd, 0, DATEDIFF(dd, 0, created));
-- clip --

AFAIK JPA / criteria API doesn't support these sorts of date functions directly, so I thought this might be possible with the CriteriaBuilder.function() method. I tried something like:

-- clip --
Expression<Date> datediff =

Expression<Date> dateadd =
datediff); builder.array(
builder.count( root.get(
)) );

query.groupBy( dateadd );
-- clip --

The bit I'm having trouble with is the datepart parameter for the SQL Server DATEDIFF / DATEADD functions. It should be a constant "dd" without the quotation marks. I tried using builder.literal("dd"), but got exception "Invalid parameter 1 specified for datediff" from the SQL Server. I think this is because literal() method adds "dd" to the query as a (quoted) String. I noticed EclipseLink has a class DateConstantExpression that might work here (javadoc: "This is different from a ConstantExpression because this value is never converted with the conversion manager and is printed out as-is"), but I'm not how I should create one. I tried:

-- clip --
Expression<String> datepart =
new ExpressionImpl<String>(em.getMetamodel(),
new DateConstantExpression("{dd}", new ExpressionBuilder()),
-- clip --

but got error message:

Exception [EclipseLink-6029] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.QueryException
Exception Description: A reference class must be provided.
Query: ReportQuery()
at org.eclipse.persistence.exceptions.QueryException.referenceC lassMissing(

Any ideas? Is it possible to use the DATEADD/DATEDIFF functions with Criteria API?


[Updated on: Tue, 02 November 2010 12:16]

Report message to a moderator

Re: Criteria API and grouping by a truncated date [message #637187 is a reply to message #636667] Thu, 04 November 2010 14:13 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

EclipseLink Expressions support adding literal syntax such as "dd" to SQL using the LiteralExpression, ExpressionBuilder.literal(), but JPA Criteria does not have this. literal() in Criteria means a constant parameter value (ConstantExpression is used).

Did you try just using another function for dd, i.e. function("dd")? It should print just dd, but you might get dd() because it is a function.

EclipseLink Expressions support both truncateDate(datePart) and addDate(datePart, number). You could use an Expression query instead of Criteria or mix the two by creating a FunctionExpressionImpl directly. It would be nice if it were easier to mix the two, such as an from(Expression) on CriteriaBuilderImpl, feel free to log a bug for this.

James : Wiki : Book : Blog : Twitter
Re: Criteria API and grouping by a truncated date [message #676855 is a reply to message #637187] Mon, 06 June 2011 18:09 Go to previous message
Ville Kaukonen is currently offline Ville KaukonenFriend
Messages: 19
Registered: January 2011
Junior Member
Did you ever get DATEDIFF to work. I'm unable to use it.
Previous Topic:Load only part of object graph
Next Topic:Cascade delete not desired, on a ManyToMany relation
Goto Forum:

Current Time: Wed Oct 14 04:07:47 GMT 2015

Powered by FUDForum. Page generated in 0.10353 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software