Home

Sort by date time in Grails App with Oracle DB

Lets say you have a domain class like the following in your Grails app and you want to list all those persons by the time that is included in the date property birthday.

class Person {
  String name
  Date birthday
}

Of cause you could just use Person.list() and write some further code to sort the result, but here I use a single slick and neat HQL line.

class PersonController {
  def list = {
    [p: Person.findAll("from Person order by hour(birthday), minute(birthday)")]
  }
}
So far so good. But if you are using an Oracle database you get to see something like this:
Hibernate operation: could not execute query;
uncategorized SQLException for SQL [...] ...
nested exception is java.sql.SQLException:
ORA-30076: invalid extract field for extract source

Unfortunatly Oracles Date Data type is not accurate enough [1] and so the Hibernate functions hour() and minute() doesnt work. To get around this problem map the birthday property to the database type timestamp.

class Person {

  static mapping = {
    birthday sqlType: 'timestamp'
  }

  String name
  Date birthday
}

If you add the mapping above everything works also with your Oracle db. Note that I used sqlType and not type to define the data type.

References

[1] Charles Miller, Hibernate, Oracle and Dates. A Story. (2005)

Date published:
Date modified: 2009-05-11
Keywords: Grails, Oracle, date, timestamp