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