Facing `org.hibernate.hql.PARSER` Error while fetching data from DB in Grails with executeQuery() method. Please tell me what am I doing wrong?

Question

I'm trying to fetch data based on dateTime but I'm facing Some Problem My Query works in MySql, But in Grails it is giving Following Error:

2015-09-11 11:59:00,697 ERROR org.hibernate.hql.PARSER:56 line 1:182: unexpected token: airDuration 2015-09-11 11:59:00,709 ERROR grails.app.controllers.com.my.test.rest.ipg.ChannelController:200 Invalid device parameter request : org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: airDuration near line 1, column 182 [ from com.my.test.ipgData.ChannelSchedule where channel = 77 and ((airDate between '2015-09-29 05:30:00' and '2015-09-29 20:30:00') or ((airDate + INTERVAL airDuration MINUTE) between '2015-09-29 05:30:00' and '2015-09-29 20:30:00')) order by airDate asc ]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: airDuration near line 1, column 182 [ from com.my.test.ipgData.ChannelSchedule where channel = 77 and ((airDate between '2015-09-29 05:30:00' and '2015-09-29 20:30:00') or ((airDate + INTERVAL airDuration MINUTE) between '2015-09-29 05:30:00' and '2015-09-29 20:30:00')) order by airDate asc ]

---

Following is My Code:

ArrayList<ChannelSchedule> channelSchedule;

Date currentDateAndTime = new Date();
String scheduleQuery =  " from " +
                        "     ChannelSchedule " +
                        " where " +
                        "     channel = 77 and ((airDate between '2015-09-29 05:30:00' and '2015-09-29 20:30:00') or ((airDate + INTERVAL airDuration MINUTE) between '2015-09-29 05:30:00' and '2015-09-29 20:30:00')) " +
                        " order by airDate asc " ;

channelSchedule = ChannelSchedule.executeQuery(scheduleQuery);

---

ChannelSchedule Class:

class ChannelSchedule {

    Date airDate;
    String airTime;
    int airDuration;
}

Answer 1

You can define derived property (The same as @Formula in java)

class ChannelSchedule {

    Date airDate;
    String airTime;
    int airDuration;
    static mapping = {
         endTime formula: 'airDate + INTERVAL airDuration MINUTE'
    }
}

and use the endTime in your query

See more here