How do i group by date only from date time field in JPQL

Question

For mysql I wrote a query like

SELECT * FROM mytable GROUP BY DATE(dateTimeField)

But i can't use the DATE() function in JPQL. Anyone have an idea how to resolve this problem?

Answer 1

If You use Hibernate under the hood you can try :

1. Create your own dialect with custom function

   public class CustomPostgresqlDialect extends PostgreSQLDialect {   
       public CustomPostgresqlDialect() {
           super();
           registerFunction("truncate_date",
           new SQLFunctionTemplate( StandardBasicTypes.TIMESTAMP, "DATE(?1)" ));
       }
   }
   

2. Register Your dialect in persistence.xml

   <property name="hibernate.dialect" value="my.own.CustomPostgresqlDialect"/>
   

3. Use your function in JPQL.

   select p from Post p group by truncate_date(p.dateCreated)
   

Answer 2

For Hibernate:

Suggestion 1:

Use cast(dateTimeField as date). See here.

Suggestion 2:

You can try to concatenate year, month and year HQL expressions.

Take a look at line 360 of this testcase.

str(year(current_date))||'-'||str(month(current_date))||'-'||str(day(current_date))

But take the time to see the generated SQL, it may (and probably will be) ugly and slow.

Answer 3

If you are using EclipseLink you can use the FUNC() function in JPQL to call a specific database function:

Support for Native Database Functions Using FUNC