Hibernate criteria group by date without time

Question

This code give me a list of two items Datetime and count. But I need group it by date without time.

Can anyone give me a clue how to do it? Thanks!

    Criteria criteria = getSession().createCriteria(Parcel.class);

    ProjectionList projectionList = Projections.projectionList(); 

        projectionList.add(Projections.groupProperty("dateCreated"));
        projectionList.add(Projections.rowCount());

    criteria.setProjection(projectionList);


    List<Object[]> results = criteria.list();

Result:

2013-04-27 11:08:00.0 | 32

2013-04-27 11:10:00.0 | 5

2013-04-28 15:03:27.0 | 12

I need:

2013-04-27 | 37

2013-04-28 | 12

Berthoud

Answer 1

You may find Projections#sqlGroupProjection method useful. It allows you to employ an SQL function date() that extracts Date form a DateTime column. Basically, instead of calling

projectionList.add(Projections.groupProperty("dateCreated"));

use

projectionList.add(Projections.sqlGroupProjection("date(dateCreated) as createdDate", "createdDate", new String[] { "createdDate" }, new Type[] { StandardBasicTypes.DATE }));

Spalla

Works great, but I had to replace the name of the entity field (dateCreated in your example) with the column name (date_created in your example). If I don’t do that, it complains that the column *in lower case*(?) (datecreated in your example) doesn’t exist. – Saurischian