CODEWITHSUNDEEP
grailsgroovy
user3245082
user3245082

Reputation: 15

Grails Filtering list by Year

I'm trying to filter list by a date format field.

class Timesheet {

    String orderNumber
    String groupCode
    String periodCode
    BigDecimal totalHours
    Date importTime
    Date generationTime // here
    String description

And the controller:

def list = {
    flash.periodCode = params.periodCode
    flash.groupCode = params.groupCode
    flash.orderNumber = params.orderNumber

    // here
    flash.generationTime = Date.parse('yyyy-mm-dd hh-mm-ss', params.generationTime)

    if(!params.max) {
        params.max = 20
    }
    def query
    def criteria = Timesheet.createCriteria()
    def results

    query = {
        and{
            like("periodCode", '%' + params.periodCode + '%')
            like("groupCode", '%' + params.groupCode + '%')
            like("orderNumber", '%' + params.orderNumber + '%')
            like("generationTime", '%' + params.generationTime + '%')
        }
    }

    results = criteria.list(params, query) 

    render(view:'index', model:[ timesheetInstanceList: results ])
}

The date looks like: 2014-01-02 08:46:14 CET.

The things that i'm trying to do is to put a text in a text field like "2014", and grails controller should find all records with date including "2014". Is there any simple way to do this ? The exception which i received is:

Unparseable date: "2013-12-31 08:02:55"
or
"java.lang.String cannot be cast to java.util.Date filtering grails"

Upvotes: 1

Views: 1449

Answers (2)

Houcem Berrayana
Houcem Berrayana

Reputation: 3080

So the simplest way to do it is to get all timesheets that were created between 1st of january of that year and the 31st of December of the same year.

First you get two instances of Dates on for 01/01/$year and the second for 31/12/$year

Calendar cal = Calendar.getInstance();
cal.set(Calendar.YEAR, year);
cal.set(Calendar.WEEK_OF_YEAR, 1);
cal.set(Calendar.DAY_OF_WEEK, 1);

Date start = cal.getTime();

//set date to last day of $year
cal.set(Calendar.YEAR, year);
cal.set(Calendar.MONTH, 11); // 11 = december
cal.set(Calendar.DAY_OF_MONTH, 31); // new years eve

Date end = cal.getTime();

Then all you need to do is to query the dates that are between start and end

query = {
        and{
            like("periodCode", '%' + params.periodCode + '%')
            like("groupCode", '%' + params.groupCode + '%')
            like("orderNumber", '%' + params.orderNumber + '%')
            like("generationTime", '%' + params.generationTime + '%')
            between('generationTime', start, end) 
        }
    }

One thing that you should care about here is to shift the hours and minutes and seconds to 23:59:59

Upvotes: 0

Andrew
Andrew

Reputation: 2249

One option is to use a Derived Property to create a non-persisted generationYear field using a SQL expression. Since this expression is raw SQL, the syntax will depend on your particular database but will follow this basic structure:

class Timesheet {
    //...
    Integer generationYear

    static mapping = {
        //...      
        generationYear formula: 'YEAR(generation_time)'
    }

You will then be able to query directly on year:

eq("generationYear", flash.generationTime[Calendar.YEAR])

Upvotes: 3

Related Questions