Doing a query for each day in a period, turning it into one query

I have this:

public Map<Day,Integer> getUniqueLogins(long fromTime, long toTime) {

  EntityManager em = emf.createEntityManager();
  try {
   Map<Day,Integer> resultMap = new ...;
   for (Day day : daysInPeriod(fromTime, toTime)) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    C开发者_开发技巧riteriaQuery<Long> q = cb.createQuery(Long.class);

    // FROM UserSession
    Root<UserSession> userSess = q.from(UserSession.class);
    // SELECT COUNT(DISTINCT userId)
    q.select(cb.countDistinct(userSess.<Long>get("userId")));
    // WHERE loginTime BETWEEN ...
    q.where(cb.between(userSess.<Date>get("loginTime"), day.startDate(), day.endDate()));

    long result = em.createQuery(q).getSingleResult();
    resultMap.put(day, (int) result);
   }
   return resultMap;
  } finally {
   em.close();
  }

 }

This executes a query for each day in a given period (the period being in the order of magnitude of a month).

Could I get this specific data in one query? I'm using Hibernate/MySQL, but I'd prefer not to need any non-standard functions.

Assuming your original query is:

SELECT COUNT(DISTINCT userId)
FROM UserSession
WHERE loginTime BETWEEN dayStart AND dayEnd;

This should return the same results as running the original one per each day of the period:

SELECT date(loginTime) AS day, COUNT(DISTINCT userId)
FROM UserSession
WHERE loginTime BETWEEN startDate AND endDate
GROUP BY day;

GROUP BY the date segment of LoginTime counting distinct userids. The back-end should provide a way to extract the date-part of the datetime value.

You'll have to use MySQL specific functions to do this.

SELECT FROM_DAYS(TO_DAYS(loginTime)) AS day, COUNT(DISTINCT userId)
FROM UserSession
WHERE loginTime BETWEEN :fromTime AND :toTime
GROUP BY day

The from_days/to_days will convert the loginTime to a number of days and then back to a datetime but with the hour/minute/second parts zero'd.