|
Custom dialects would certainly work, but custom dialects put us back into the business of havnig to add another custom dialect every time we add a supported platform. We had a custom persistence layer that we ripped out in favor of Hibernate specifically to avoid that. We support three major database platforms currently, and are planning to add more. The date operations you list don't seem to work in HQL. I created an IntervalTracker class (attached). Using the following query returns quite different results on different db platforms: List results = executeQuery("select endDate - startDate from IntervalTracker order by id"); using MySQL this query produced a result like 1.111739E8. Using two Calendar objects to mimic the values in the database, subtracting the getTimeInMillis() values returned 3605940000. Using HQLDB returned an exception: Using PostgreSQL also returned an exception: Attaching a patch containing function defintions for "seconds_between" and "milliseconds_between" for the following dialects: DB2Dialect The patches look great. Why aren't these being considered for 3.3? I updated my MySql and Oracle java dialects using the latest SVN source code, compiled them, and updated my hibernate3.jar with these class files respectively. When I run seconds_between(date1,date2) method, both Oracle and MySql throw no data type for node error in hibernate tools HQL editor. Am I missing something or do I need to update anything else? Oracle Exception stacktrace:
I am able to run dialect specific methods with no issues for example I can run datediff(date1, date2) when connected to my MySql database and run months_difference(date1, date2) for Oracle dialect, however Thanks a bunch I am really sorry about my earlier comment, the patch works like a charm when used in my HQL within my application I wonder why this issue is open since 2007 and still not resolved or even in progress. Thank you! |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Why not create custom dialects? At the very least, we need to know exactly for which operations you are requesting support.
DATETIME (the spec term for DATE, TIME, OR TIMESTAMP types) arithmetic is in fact defined by the ANSI SQL spec. Section 4.5.3 from the '92 spec defines this behavior. Basically:
DATETIME - DATETIME = INTERVAL
DATETIME +|- INTERVAL = DATETIME
INTERVAL + DATETIME = DATETIME
INTERVAL +|- INTERVAL = INTERVAL
INTERVAL *|/ NUMERIC = INTERVAL
NUMERIC * INTERVAL = INTERVAL
are all defined behaviors. I cannot say if all databases implement this all correctly however. And I am pretty sure some treat it differently in terms of what is actually calculated and returned in terms of the INTERVAL returns.