0 Replies Latest reply on Oct 27, 2006 5:28 PM by Jeremy Green

    Group by Date/Timestamp part in EJBQL?

    Jeremy Green Newbie

      Hey everybody, I was wondering if there's a way to group by just a part of a date field using EJBQL. Basically I want to get a report of the total number and total value of all transactions that happened over a year, grouped by month. I've tried the following query, but it doesn't do the trick. Does anybody have any ideas?

      I have a Transaction Entity with an attribute called transTime which is a java.util.Date.

      I tried and failed with this:
      "select count(t.id),sum(t.amount),t.transTime.getMonth() from Transaction t group by t.transTime.getMonth()"

      I also tried the same thing but with the 'getMonth()' as 'getMoth' just to see what happened. No luck there either.

      I do have it working by just issuing one query for each month over the time period I'm interested in looking at, but I'm hoping to find a way to offload some of that to the DB, especially since I need to be able to genereate these kind of stats at the daily level over different time spans.

      Any help would be appreciated.

      Thanks,
      Jeremy