The following is a sample code from chapter 14.15 of the reference



Now, assume there is a registeration date attribute in the user entity. I want to list the users by the order of the average number of messages per day. I know that I can pass in the current date, but how to use an elapsed time in the HQL? Here is an article on how to cacalculate elapsed time in Java:

http://www.javaworld.com/javaworld/jw-0 ... -time.html

Any thought?

Thanks.

I'd probably workout "day - 1" in java, but if you want to do it in HQL, use the various date functions described in section 14.9, "Expressions", of the ref docs:Obivously the logic is wrong, there's no handling of times, first day of month or year, etc., but you get the idea. The HQL logic will be messy, but java Calendars can subtract one day really easily, so IMO you should do the date maths there.

I use a simple trick that works - I map my Date fields to columns of type "long" (we wrote a special user type for this). With this user type in place, I can do the math the same way I do it in Java. An added bonus is that our product works with databases with imprecise date/time types (e.g. MySQL which stores dates with resolutuion too coarse for what we're doing).

Let me know if this helps.

Thanks both for the information.

I need to study it since I hadn't see the expression tenwit provided before. And I don't think I will change the current date type data mapping.

After some study, I don't think the tenwit's method won't work since elapsed time calculation is not that simple. Ideally, retrieving the registration date, calculating the elapsed time, and using the result to obtain a user list should be in a single HQL. That doesn't seem to be possible. I am going to create an age entity to keep track of the number of days of each user. A scheduled job will keep the age updated. In this way, I can keep the elapsed time calculation outside of a HQL and obtain the result from entity/table.