Fulfillment of the request for each day in one period, turning it into one request

Question

I have it:

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();
    CriteriaQuery<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();
  }

 }

Fulfills a request for each day for a certain period (the period is about a month).

Can I get this specific data in one query? I use Hibernate / MySQL, but I would prefer not to use any non-standard functions.

Answer 1

Assuming your original request:

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

This should return the same results as the original for each day of the period:

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

Answer 2

LoginTime, . - datetime.

Answer 3

MySQL.

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

From_for / to_days converts loginTime to a few days, and then back to a date-time, but with hours / minutes / seconds zero.