Grails accessing data where dates equal in database first mode

Question

I'm returning to Grails after a year hiatus while I worked for .Net shop. I had always programmed Grails in code first mode. My newest project is database first, and I thought I was ready. I have a simple calendar table in my MySql database:

commit;CREATE TABLE `Calendar` (
  `ID` bigint(20) NOT NULL,
  `Title` varchar(200) NOT NULL,
  `EventDate` date NOT NULL,
  `StartTime` time DEFAULT NULL,
  `EndTime` time DEFAULT NULL,
  `Location` varchar(500) NOT NULL,
  `version` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With the following data in the table:

insert into Calendar (Title, EventDate, StartTime, EndTime, Location, version)
values ('Summer 2016 Garage Sale', '2016-07-23', '7:00 ', '19:00', 'Lodge', 0);

insert into Calendar (Title, EventDate, StartTime, EndTime, Location, version)
values ('August 2016 Member Meeting', '2016-08-03', '19:00', '20:00', 'Lodge', 0);

commit;

My calendar class in Grails looks like:

package plaquemineelks

import java.sql.Time
import grails.validation.Validateable

@Validateable
class Calendar {

    Long id
    Integer version

    String title
    Date eventDate
    Time startTime
    Time endTime
    String location

    static constraints = {
        title (blank: false)
        eventDate (blank: false)
        location (blank: false)
    }

    static mapping = {
        table "Calendar"
        id column: "ID"
        version column: "version"
        eventDate column: "EventDate"
        startTime column: "StartTime"
        endTime column: "EndTime"
        location column: "Location"
    }
}

My controller looks like:

package plaquemineelks

class CalendarController {

def index() { }

    def getByDate(String EventDate) {
        def Date newDate = Date.parse("mm/dd/yyyy", EventDate)
        def results = Calendar.findAllByEventDate(newDate)
        render(contentType: 'text/json') {[
            'results': results,
            'status': results ? "OK" : "Nothing present"
        ]}
    }
}

When I run the app and open the URI

http://localhost:8080/PlaquemineElks/Calendar/getByDate?EventDate=07/23/2016

My Json looks like:

{"results":[],"status":"Nothing present"}

I have tried a variety of formats, etc. for the date, keeping it groovy. I'm sure I'm missing something simple.

Thanks for any assistance.

Answer 1

You are inserting data to your table using sql query which means by default the StartDate contains date only and 00:00:00 for timestamp. Otherwise it will consider the timezone also if you insert into it using GORM.

First thing that you need to do is set the time zone to UTC for your application. In Bootstrap.grooy:

TimeZone.setDefault(TimeZone.getTimeZone('UTC'))

Secondly the format mm/dd/yyyy that you are using to parse the date is wrong, it should be: MM/dd/yyyy.

println Date.parse("mm/dd/yyyy", "07/23/2016")
Sat Jan 23 00:07:00 UTC 2016 //wrong

println Date.parse("MM/dd/yyyy", "07/23/2016")
Sat Jul 23 00:00:00 UTC 2016 //correct