Grouping by day from date using JPA Criteria API?

Question

Java

The challenge is to group the events by a specific day, to do this I have to pull the day out of the date and group the entries by it. Previously we used MySql and this code worked as it should:

public static Specification<AllFile> findByGroup(FileFilter fileFilter) {
return (Specification<AllFile>) (root, query, cb) -> {
    final Collection<Predicate> predicates = new ArrayList<>();

    if (fileFilter.getStart() != null && fileFilter.getEnd() != null) {
        Date date = new Date(fileFilter.getStart().getTime());
        date.setHours(0);
        date.setMinutes(0);
        date.setSeconds(0);
        date.setDate(1);

        predicates.add(cb.greaterThanOrEqualTo(root.get("created"), date));
        predicates.add(cb.lessThanOrEqualTo(root.get("created"), fileFilter.getEnd()));
    }
    else if(fileFilter.getStart() != null) {
        Date date = new Date(fileFilter.getStart().getTime());
        date.setHours(0);
        date.setMinutes(0);
        date.setSeconds(0);
        date.setDate(1);

        Date newDate = DateUtils.addMonths(date, 1);

        predicates.add(cb.greaterThanOrEqualTo(root.get("created"), date));
        predicates.add(cb.lessThanOrEqualTo(root.get("created"), newDate));
    }

    if (fileFilter.getRoute() != null) {
        predicates.add(cb.equal(root.get("route"), fileFilter.getRoute()));
    }

    if (fileFilter.getDevice() != null) {
        predicates.add(cb.equal(root.get("device"), fileFilter.getDevice()));
    }

    if (fileFilter.getType() != null) {
        predicates.add(cb.equal(root.get("type"), fileFilter.getType()));
    }

    if (fileFilter.getUser() != null) {
        predicates.add(cb.equal(root.get("user"), fileFilter.getUser()));
    }

    if (fileFilter.getDeleted() != null) {
        predicates.add(cb.equal(root.get("deleted"), fileFilter.getDeleted()));
    }



    query.groupBy(cb.function("day", Date.class, root.get("created")));
    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
};
}

But after switching to Postgres, this approach stopped working. to work correctly I need to send something like this:

select extract(day from allfile0_.created) as dd
from all_file allfile0_ 
where allfile0_.created>='Tue Oct 01 00:00:00 MSK 2019' and allfile0_.created<='Fri Nov 01 00:00:00 MSK 2019' 
group by dd

Instead of this:

select allfile0_.id as id1_0_, allfile0_.added as added2_0_, 
allfile0_.comment as comment3_0_, 
allfile0_.created as created4_0_, allfile0_.deleted as deleted5_0_, 
allfile0_.device_id as 
device_13_0_, allfile0_.duration as duration6_0_, allfile0_.mark as 
mark7_0_, allfile0_.path as 
path8_0_, allfile0_.recognition as recognit9_0_, allfile0_.route_id as 
route_i14_0_, allfile0_.size 
as size10_0_, allfile0_.type as type11_0_, allfile0_.updated as 
updated12_0_, allfile0_.user_id as 
user_id15_0_ 

from all_file allfile0_ 

where allfile0_.created>='Tue Oct 01 00:00:00 MSK 2019' and allfile0_.created<='Fri Nov 01 00:00:00 MSK 2019' 
group by extract(day from allfile0_.created)

Is it possible to get a query like I need with the Criteria API? Need advice on custom select to select exactly what I need.

Answer 1

Changed
on the

query = cb.createTupleQuery();
            root = query.from(AllFile.class);
            query.select(root.get("created")).
                    groupBy(cb.function("day", Integer.class, root.get("created")));

After that the code worked as it should