how to create Criteria of Group By Month of Date Field + Grails

Question

I want to get count of users grouped on a month basis from today to 365 days back. I am using groovy with Hibernate Criteria builder API . Any easy way to do this grouping? how do we specify the date format for grouping the date field by month?

Right now I have the following:

             def con_cnt =Users.createCriteria().list {
            like('employeeid','c_%')
            between('sunsetdate', fromDate, toDate)
            projections {
                count('employeeid')
                //groupProperty('sunsetdate')

            }
        }

The groupProperty('sunsetdate') groups it on a date basis and even includes the time in the grouping .. so the counts are cacluated for a very unique date & time which makes counts 1 same as the source table. How do we specify date formats in grouping using this approach? or do I have to use HQL?

Thanks in Advance.

Answer 1

you can visit this here for further details

Create three new numeric fields each for week,month and year in the domain class. These fields won't be mapped to column in the table. Provide static mapping for the three fields.

      static mapping = {

    week formula('WEEK(DATE_OF_EXPENSE)')    //provide the exact column name of the date field
    month formula('MONTH(DATE_OF_EXPENSE)')
    year formula ('YEAR(DATE_OF_EXPENSE)')

    }
    def results = c.list {
  between("dateOfExpense", fromDate, toDate) 
  projections {
    switch(groupBy){
        case "week":
           groupProperty('year')
           groupProperty('month')
           groupProperty('week') 
        break;
        case "month"
           groupProperty('year')
           groupProperty('month')
        break;
        case "year":
           groupProperty('year')
        break;
    }        
    sum('amount')
  }
}