Hi Rob, Whenever I have had to do something like this I resort to using Joda time ( http://joda-time.sf.net) in my model classes. Waaaaay easier date/time manipulation, and makes this sort of thing a little easier if you can do it in code. Extra incentive for us is that we use XML in our architectures and Joda time makes the conversion to XML painless.
Mike. On Dec 7, 2007 7:06 PM, Rob Hills <[EMAIL PROTECTED]> wrote: > Hi Michael, > > Michael Horwitz wrote: > > > > > > On Dec 7, 2007 5:37 PM, Rob Hills <[EMAIL PROTECTED] > > <mailto:[EMAIL PROTECTED]>> wrote: > > > > Hi All, > > > > This is really a Hibernate column, but hopefully someone here will > > know > > more about it than I do, which wouldn't be hard. > > > > I have a model class that includes an attribute "departureTime" of > > type > > java.util.Date and the data contains both date and time > > information. In > > my Dao class, I obtain a list of this object using Hibernate and I > > need > > to sort it by the date component of the data (ie excluding the > time). > > > > I've read the Hibernate documentation on HQL and AFAICT, HQL > supports > > expressions, but only in the Select part of the HQL. In the > > section on > > "Order By", the documentation says that HQL will order a list "by > any > > property of a returned class or component". So, I created an > > @Transient > > attribute "departureDate" + getter, that returned just the date > > part of > > my data (in fact a string formatted to "yyyymmdd"), but that > > caused an > > exception to be raised "could not resolve property departureDate". > > > > > > Perhaps a small ray of light: the bit on Hibernate supporting > > functions in the order by clause is in the next section of the > > document 14.12: > > > > SQL functions and aggregate functions are allowed in the having and > > order by clauses, if supported by the underlying database (eg. not in > > MySQL). > > > > So could be done as long as you are not using MySql? > Well, MySql is one of the databases I'm not using for this project, so > that part's OK. Thanks for pointing out that little bit of the docco, > I'd stopped reading at the end of the previous "Order By" paragraph :-( > Because I'm trying to keep this "generic" HQL, I tried to incorporate an > earlier section of that document (14.10) on expressions with something > else I'd found in my searching that looked promising, the @Formula > annotation. Unfortunately that hasn't worked. I tried defining a > "departureDate" in my pojo with the following: > > @Formula("year(departureTime) || month(departureTime) || > day(departureTime)") > public String getDepartureDate() {... > > But that gives me an error along the lines of "no such function > year(Timestamp without timezone)". Having seen some examples of use of > the Hibernate @Formula annotation, it looks like they want raw SQL there > rather than HQL. Unfortunately, it is in handling and formatting of > dates that different databases seem to be most divergent and I need to > make this project as DB-agnostic as I can :-( > > I may have to create a function to give me a sortable date string from a > timestamp field. Now it would be nice if there were some way of > defining that within Hibernate! > > Cheers, > Rob Hills > Waikiki, Western Australia > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
