Saving and Querying a Date in GORM Grails

Question

I have a database table TableA, which has a column 'theDate' for which the datatype in the database is DATE.

When I save a java.util.Date to 'theDate' through GORM it appears to save just the date value when I look at the data in the table by just executing select * from TableA.

However, when I run a query such as:

select * from TableA where theDate = :myDate

No results are found, but if I run something like;

select * from TableA where theDate <= :myDate

I do get results.

So it's like the Time is relevant.

My question is how do I save a Date and query for a Date ignoring the Time completely and just matching on an exact Date only?

Thanks.

note: I have also tried using sql.Date and util.Calendar but to no success.

Answer 1

clearTime()

You can use clearTime() before saving and before comparing to zero out the time fields:

// zero the time when saving
new MyDomain(theDate: new Date().clearTime()).save()

// zero the target time before comparing
def now = new Date().clearTime()
MyDomain.findAll('SELECT * FROM MyDomain WHERE theDate = :myDate', [myDate: now])

joda-time plugin

An alternative would be to install the joda-time plugin and use the LocalDate type (which only holds date information, no times) instead of Date. For what it's worth, I don't think I've worked on a project with dates without using the Joda plugin. It's completely worth it.

Answer 2

If you have date saved without clearing you could retrieve it using range, as Jordan H. wrote but in more simple way.

def getResults(Date date) {

    def from = date.clearTime()
    def to = from + 1

    def results = MyDomain.findAll("from MyDomain where dateCreated between :start and :stop" ,[start:from,stop:to])

}

Answer 3

You can use the DB type date not datetime , in the filed type

Answer 4

Your question may be a duplicate. See Convert datetime in to date. But if anyone has more recent information, that would be great.

If that doesn't help, you can hack it the way I might, with a BETWEEN restriction, e.g.

def today = new Date()
def ymdFmt = new java.text.SimpleDateFormat("yyyy-MM-dd")
def dateYmd = ymdFmt.format(today)
def dateTimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
def startDate = dateTimeFormat.parse("${dateYmd} 00:00:00");
def endDate = dateTimeFormat.parse("${dateYmd} 23:59:59");
MyDomain.findAll("from MyDomain where dateCreated between ? and ?", [startDate, endDate])

It's definitely not pretty, but it may get you where you're going.

Answer 5

I figured it out.

I used DateGroovyMethods.clearTime to clear the time value before saving.