CODEWITHSUNDEEP
grailshqlgrails-ormgrails-2.0
lrkwz
lrkwz

Reputation: 6513

Gorm count elements group by day without time

I wish to retrieve a list of UserProfile registrations per day. The domain object UserProfile stores a Date creationDate property. I've tried

def results = UserProfile.executeQuery('select u.creationDate, count(u) from UserProfile as u group by u.creationDate')
println results

which obviously is not what I need because data is (already) stored with complete time in it.

Any resource savvy solution will fit: projections, hql, ...

Thnks

Upvotes: 0

Views: 2416

Answers (3)

lukelazarovic
lukelazarovic

Reputation: 1500

I use HQL cast function:

def results = UserProfile.executeQuery("""
  select cast(u.creationDate as date), count(u) 
  from UserProfile as u 
  group by cast(u.creationDate as date)
""")

Underlying database must support ANSI cast(... as ...) syntax for this to work, which is the case for PostgreSQL, MySQL, Oracle, SQL Server and many other DBMSs

Upvotes: 2

Ian Roberts
Ian Roberts

Reputation: 122364

You could define a "derived" property mapped as a formula to extract the date part of the date-and-time. The exact formula will differ depending what DB you're using, for MySQL you could use something like

Date creationDay // not sure exactly what type this needs to be, it may need
                 // to be java.sql.Date instead of java.util.Date

static mapping = {
  creationDay formula: 'DATE(creation_date)'
}

(the formula uses DB column names rather than GORM property names). Now you can group by creationDay instead of by creationDate and it should do what you need.

Or instead of a "date" you could use separate fields for year, month and day as suggested in the other answer, and I think those functions are valid in H2 as well as MySQL.

Upvotes: 0

dmahapatro
dmahapatro

Reputation: 50245

Break down the date to day, month and year then ignore the timestamp.

This should give you what you need.

def query = 
"""
select new map(day(u.creationDate) as day, 
               month(u.creationDate) as month, 
               year(u.creationDate) as year, 
               count(u) as count)
       from UserProfile as u
       group by day(u.creationDate), 
                month(u.creationDate), 
                year(u.creationDate)
"""

//If you do not worry about dates any more then this should be enough
def results = UserProfile.executeQuery( query )

//Or create date string which can be parsed later
def refinedresults = 
    results.collect { [ "$it.year-$it.month-$it.day" : it.count ] }

//Or parse it right here
def refinedresults = 
    results.collect {
        [ Date.parse( 'yyyy-MM-dd', "$it.year-$it.month-$it.day" ) : it.count ]
    }

Upvotes: 0

Related Questions