Jodatime groovy/grails daterange search with TimeZone conversion

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?

Answers


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));
    }

Need Your Help

Why does my SQL query return rows with NULL? It should never return rows with NULL

sql

Suppose I have a SQL table called AT_Devices with each record representing a piece of hardware. I have a second table called AT_Event_History which describes "events" that occur for pieces of hard...

c# does 1.09:00:00 timespan mean 9am tomorrow?

c# datetime timer timespan

I have a routine which is suppose to run at 9am everyday....