JPQL Group By TimeStamp as a Date

Question

In a JPA application the Bill entity has an attribute named createAt with a TimeStamp annotation.

I want to group Bills by createdAt by Date only, not by TimeStamp.

In the query, although I pass TemporalType as Date, still bills are grouped as Time.

How can I group Bills by date using the createdAt attribute.

I use JPA with EclipseLink 2.5.

The Bill Entity

@Entity
public class Bill implements Serializable {
    ....
    ....

    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    Date createdAt;

    ....
    ....
}

This is the method used to list bills.

public String listProfitBillsDailySummery() {
    System.out.println("list profit bills");
    String jpql = "SELECT new com.divudi.data.DailySummeryRow(b.createdAt, b.freeValue, b.netTotal, b.discount) "
            + " FROM Bill b "
            + " WHERE (type(b)=:bc1 or type(b)=:bc2 or type(b)=:bc3 ) "
            + " and b.retired=false "
            + " and (b.billType=:bt1 or b.billType=:bt2 or b.billType=:bt3) "
            + " and b.createdAt between :fromDate and :toDate ";

    Map temMap = new HashMap();

    if (department != null) {
        jpql += " and b.department=:d ";
        temMap.put("d", department);
    }

    jpql += " group by b.createdAt "
            + "order by b.deptId  ";

    temMap.put("bc1", BilledBill.class);
    temMap.put("bc2", RefundBill.class);
    temMap.put("bc3", CancelledBill.class);

    temMap.put("bt1", BillType.PharmacyPurchaseBill);
    temMap.put("bt2", BillType.PharmacyGrnBill);
    temMap.put("bt3", BillType.PharmacySale);

    temMap.put("fromDate", getFromDate());
    temMap.put("toDate", getToDate());

    List<Object[]> dsso = getBillFacade().findAggregates(jpql, temMap, TemporalType.DATE);
    profitTotal = 0.0;
    discountTotal = 0.0;
    freeTotal =0.0;
    dailySummeryRows = new ArrayList<>();
    for (Object b : dsso) {
        DailySummeryRow dsr = (DailySummeryRow) b;
        profitTotal += dsr.getProfit();
        discountTotal += dsr.getDiscounts();
        freeTotal += dsr.getFreeAmounts();
        dailySummeryRows.add(dsr);
    }
    return "pharmacy_report_gross_profit_by_bills_ds";
}

This is the method in the Session Bean

public List<Object[]> findAggregates(String temSQL, Map<String, Object> parameters, TemporalType tt) {
        TypedQuery<Object[]> qry = getEntityManager().createQuery(temSQL, Object[].class);
        Set s = parameters.entrySet();
        Iterator it = s.iterator();
        while (it.hasNext()) {
            Map.Entry m = (Map.Entry) it.next();
            Object pVal =  m.getValue();
            String pPara = (String) m.getKey();
            if(pVal instanceof Date){
                Date pDate = (Date) pVal;
                qry.setParameter(pPara, pDate, TemporalType.DATE);
            }else{
                qry.setParameter(pPara, pVal);
            }
        }
        try {
            return qry.getResultList();
        } catch (Exception e) {
            System.out.println("e = " + e);
            return null;
        }
    }

The difference between `timestamp` and `date` is not what you think. check it here: http://stackoverflow.com/questions/2305973/java-util-date-vs-java-sql-date — pms, Aug 26 '14 at 18:52
Thanks. I record Bills with the created time. But I want to get the details of bills, like total or discount, grouped by Date. That is the issue I am going to address. — Buddhika Ariyaratne, Aug 26 '14 at 18:58
then you need to extract the date from the column when you `group by` — pms, Aug 26 '14 at 19:01

Buddhika Ariyaratne · Answer 1 · 2014-08-27T10:46:51.130

I used FUNC('Date',b.createdAt) to group by date.

public String listProfitBillsDailySummery() {
    System.out.println("list profit bills");
    String jpql;
     jpql = "SELECT new com.divudi.data.DailySummeryRow(FUNC('DATE',b.createdAt), b.freeValue, b.netTotal, b.discount) "
            + " FROM Bill b "
            + " WHERE (type(b)=:bc1 or type(b)=:bc2 or type(b)=:bc3 ) "
            + " and b.retired=false "
            + " and (b.billType=:bt1 or b.billType=:bt2 or b.billType=:bt3) "
            + " and FUNC('DATE',b.createdAt) between :fromDate and :toDate ";

    Map temMap = new HashMap();

    if (department != null) {
        jpql += " and b.department=:d ";
        temMap.put("d", department);
    }

    jpql += " group by FUNC('DATE',b.createdAt) "
            + "order by FUNC('DATE',b.createdAt)  ";

    temMap.put("bc1", BilledBill.class);
    temMap.put("bc2", RefundBill.class);
    temMap.put("bc3", CancelledBill.class);

    temMap.put("bt1", BillType.PharmacyPurchaseBill);
    temMap.put("bt2", BillType.PharmacyGrnBill);
    temMap.put("bt3", BillType.PharmacySale);

    temMap.put("fromDate", getFromDate());
    temMap.put("toDate", getToDate());

    List<Object[]> dsso = getBillFacade().findAggregates(jpql, temMap, TemporalType.DATE);
    profitTotal = 0.0;
    discountTotal = 0.0;
    freeTotal =0.0;
    dailySummeryRows = new ArrayList<>();
    if(dsso==null){
        dsso = new ArrayList<>();
        System.out.println("new list as null");
    }
    for (Object b : dsso) {
        DailySummeryRow dsr = (DailySummeryRow) b;
        profitTotal += dsr.getProfit();
        discountTotal += dsr.getDiscounts();
        freeTotal += dsr.getFreeAmounts();
        dailySummeryRows.add(dsr);
    }
    return "pharmacy_report_gross_profit_by_bills_ds";
}

that's cool. But remember `Func` is vendor (EclipseLink) specific and not coming from pure JPA. So if you change your ORM, you probably need to change the query too. — pms, Aug 26 '14 at 20:22

JPQL Group By TimeStamp as a Date

1 Answers1

Linked