I need a query to convert time (milisec) to an hourly distribution.

my table has a colum called timestamp : value in milisec.
to group on hours I need to do the folowing
devide by 1000 -> sec devide by 3600 -> hours -> mod 24 -> get the hour of day the records was created.

timestamp is a long:

```
    public static volatile SingularAttribute<VersionJpa, Long> timestamp;

```

I can then display the results in a in day distribution. However, this doesn't work, the group by not working and I get the full list not grouped and not counted.

```
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<VersionJpa> root = cq.from(VersionJpa.class);
    cq.where(p);
    Expression<Integer> group = cb.mod(cb.toInteger(cb.quot(root.get(VersionJpa_.timestamp), 3600000)),24);
    cq.multiselect(group, cb.count(root));
    cq.groupBy(group);
    TypedQuery<Tuple> tq = em.createQuery(cq);
    return tq.getResultList();

```

this SQL (mysql) generate the right results. is there equivalent floor in JPA?
Tried as.(Integer) || toInteger()

```
    select mod(floor(stamp/3600000), 24) , count(*) from versions group by mod(floor(stamp/3600000), 24);

```

I found that during unit test (on Derby) this runs well, but in more production like setup (MySQL) it doesn't cast the inner most aggregate to integer, so the group is done on a floating values and not on 24 (0, 1, .. 23) potential values (return thousand of groups).

```
    Expression<Integer> hours = cb.mod(cb.quot(root.get(VersionJpa_.timestamp).as(Integer.class), 3600000).as(Integer.class),24).as(Integer.class);
    cq.multiselect(hours, cb.count(root));
    cq.groupBy(hours);

```

adding PostgreSQL support and testing lead to yet another error which enforce my thinking that my query is wrong:

```
    RuntimeException Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: column "versions.stamp" must appear in the GROUP BY clause or be used in an aggregate function Position: 13 Error Code: 0 Call: SELECT MOD((STAMP / ?), ?), COUNT(ID) FROM VERSIONS GROUP BY MOD((STAMP / ?), ?) LIMIT ? OFFSET ? bind => [6 parameters bound] Query: TupleQuery(referenceClass=VersionJpa sql="SELECT MOD((STAMP / ?), ?), COUNT(ID) FROM VERSIONS GROUP BY MOD((STAMP / ?), ?) LIMIT ? OFFSET ?")

```

any help is appreciated.