Jodatime groovy/grails daterange search with TimeZone conversion

Question

I am currently storing Joda DateTime (Datetime+TimeZone) values into a database.

What I want to do grab 24 hours of records based on the timezone of the user, rather than UTC time. I.E. I want to grab the records between 00:00GMT-24:00GMT for people whose timezone is GMT, and 00:00PST-24:00PST for people whose timezone is PST.

Now I dont think I can do this in a HQL query, as obviously HQL is not going to perform a DateTime.toDateTime(toZone) conversion for me. So is it possible to perform this kind of range search in groovy/grails?

Answer 1

I thought about this a little more, and the solution might be to attack it another way. Instead of trying to perform the toDateTime(zone) at the hibernate level, do it at a higher level - i.e. on the creation of the start->end search criteria;

    DateTimeZone toZone = DateTimeZone.forID("Europe/London");
    DateTime now = new DateTime()
    DateTime today = new DateTime(now.year, now.monthOfYear, now.dayOfMonth, 0, 0, 0, 0)
    DateTime yesterday = today.minusDays(1)

    def queryStr = "from JODATesting j where j.thedate > :date1 and j.thedate < :date2"
    for (JODATesting joda: JODATesting.executeQuery(queryStr, [date1: yesterday, date2: today]) ) {
        println("##### Joda"+joda.id +"=" +joda.thedate.toDateTime(toZone));
    }