hql query for getting record for last 12 hours

Question

I have a table to record login activity. In my app dashboard, I want to show the last logins in a graph. Number of user logged in hourly interval. The query is to return result like this:

time                         count
-------                      --------
9.00.00 - 9.59.59            1
10.00.00 - 10.59.59          0
11.00.00 - 11.59.59          13
12.00.00 - 12.59.59          7
13.00.00 - 13.59.59          4

The entity(LoginActivity)has dateLastUpdated field, on which the query should work on. What will be the query to get expected result? A proper native workaround query will also be appreciated.

Answer 1

Try this

Query query = session.createQuery("select la from LoginActivity la where la.dateLastUpdated > :date");
Calendar cal = Calendar.getInstance();
cal.add(Calendar.HOUR, -12);
Date date = cal.getTime();
query.setParameter("date", date);
List result = query.list();

Here's a shorter example

Query query = session.createQuery("select la from LoginActivity la where la.dateLastUpdated > :date");
query.setParameter("date", new Date(System.currentTimeMillis() - 12*60*60*1000);
List result = query.list();

EDIT

Following Guillaume Polet's example (upvoted), it turns out there is a JPA way to do this (much better than having to use database vendor specific SQL functions). This code is working for me

    String query = "select hour(la.dateLastUpdated) as hour, count (la) from LoginActivity la " +
        "where la.dateLastUpdated > :date " +
        "group by hour(la.dateLastUpdated) " +
        "order by hour(la.dateLastUpdated)";

    List<Object[]> objects = getSession().createQuery(query).setParameter("date", date).list();

    for (Object[] o : objects) {
        Object hour = o[0];
        Object count = o[1];
        System.out.println(hour + ".00.00 - " + hour + ".59.59\t" + count);
    }