Hi Michael,
Michael Horwitz wrote:
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.
I'd looked previously at joda-time and found it interesting, but had
discarded it as I didn't see a major need for it and have been trying to
minimise the number of different components in my project. However,
this whole date/time business is becoming bigger than Ben Hur ATM :-(
Part of my problem is that when previously using the java.util.Date
class for dates and times, I was getting weird timezone-related
problems. The app is only ever going to be run in one timezone so I
didn't expect to have to worry about TZ stuff, but between the server OS
TZ, the JVM TZ and the Database TZ, I was having all kinds of problems
with time/date values changing (sometimes several times) between data
entry, storage and retrieval. Also, DB queries based on date parameters
were not working as expected, etc. etc. I'm developing against
Postgresql, but the target test and production DB is Oracle (which has
its own date/time peculiarities) and the spec calls for the app to be DB
agnostic.
Since I last emailed on this, I'd decided to store dates and times
separately, as long values (date in yyyyMMdd format and time in HHmm
format). At least it stops the various layers from "helpfully"
adjusting the times for me. However, I've not been able to find a way
to format the output using standard formatting classes based on pattern
strings from my properties bundle. I've been trying to use
java.text.DecimalFormat with a pattern like "00:00" for a time display
but it seems to interpret the colon as a decimal character, which would
mean I'd need to store the time as a decimal rather than a long. I
could cope with that, but I can see that I won't be able to get
DecimalFormat to format a yyyyMMdd Long value to look like a date. It
looks like my only alternatives are to convert my Long values to
java.util.Date values (and ? reintroduce TZ issues, as well as
performance issues converting things back and forth) or else to do my
own hard-coded formatting and forget about trying to make that
re-definable via a pattern in the resource bundle.
So, I'll take a closer look at joda-time to see if that can help me.
However, I worry that if I return to storing these values in the DB as
any kind of Date/Time value, I'll get my TZ problems back.
I'll report back what I discover.
On Dec 7, 2007 7:06 PM, Rob Hills <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Michael Horwitz wrote:
> On Dec 7, 2007 5:37 PM, Rob Hills <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
> <mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>>> wrote:
> 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!
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
