CODEWITHSUNDEEP
sqloracle-databasehibernatejpajpql
Fabio
Fabio

Reputation: 21

JPQL :grouping by date

I have a table with several records for each day, and each record has a column that represents the datetime. I want to select the count of the records for each day, so I want to create in JPQL a query like the following one:

SELECT COUNT(*) FROM ORDERS o GROUP BY TRUNC(o.ORDER_DATE)

Unfortunately, in JPQL there isn't a function like TRUNC... :( How can I do this?

Upvotes: 1

Views: 3195

Answers (4)

Nitin Jha
Nitin Jha

Reputation: 1871

Use cast! That will work 

@Query("select new DBClass(COUNT(c.field1) as count,cast(c.created as date)) from Table c GROUP BY cast(c.created as date)")

Upvotes: 2

gmaslowski
gmaslowski

Reputation: 764

I think that should to the trick:

SELECT COUNT(*) FROM ORDERS o GROUP BY FUNC('TRUNC', o.ORDER_DATE)

Upvotes: 0

Steve Ebersole
Steve Ebersole

Reputation: 9443

Hibernate when interpreting JPQL queries understands any of the functions it knows about. If the Dialect you are using understands TRUNC, you will be fine. If not, create a custom dialect to register that TRUNC function.

Upvotes: 0

user902383
user902383

Reputation: 8640

I'm not sure is it works, but did you use method function? it will be something like

Expression<Date> truncExpr = cb.function("TRUNC",Date.class,orders.get("orderDate"),"MM");
criteria.groupBy(truncExpr);

Upvotes: 0

Related Questions