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.

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:
Caused by: java.sql.SQLException: Wrong data type in statement [select intervaltr0_.enddate-intervaltr0_.startdate as col_0_0_ from intervaltracker intervaltr0_ order by intervaltr0_.id]
(full stack trace attached)

Using PostgreSQL also returned an exception:
Caused by: org.postgresql.util.PSQLException: Bad value for type double : 41 days 17:39:00
(full stack trace attached)

HSQLDB stack trace

PostgreSQL stack trace

Attaching a patch containing function defintions for "seconds_between" and "milliseconds_between" for the following dialects:

DB2Dialect
DerbyDialect
HSQLDialect
MySQL5Dialect
Oracle9Dialect
PostgreSQLDialect
SQLServerDialect
SybaseDialect

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:
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'seconds_between' {originalText=seconds_between}
-[EXPR_LIST] SqlNode: 'exprList'
+-[DOT] DotNode: 'stockoptio0_.so_date' {propertyName=soDate,dereferenceType=4,propertyPath=soDate,path=so.soDate,tableAlias=stockoptio0_,className=database.StockOption,classAlias=so}
| +-[ALIAS_REF] IdentNode: 'stockoptio0_.so_id' {alias=so, className=database.StockOption, tableAlias=stockoptio0_}
| -[IDENT] IdentNode: 'soDate' {originalText=soDate}
-[DOT] DotNode: 'stockoptio0_.record_added_date_time' {propertyName=recordAddedDate,dereferenceType=4,propertyPath=recordAddedDate,path=so.recordAddedDate,tableAlias=stockoptio0_,className=database.StockOption,classAlias=so}
+-[ALIAS_REF] IdentNode: 'stockoptio0_.so_id' {alias=so, className=database.StockOption, tableAlias=stockoptio0_}
-[IDENT] IdentNode: 'recordAddedDate' {originalText=recordAddedDate}

MySql Exception stacktrace:
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'seconds_between' {originalText=seconds_between}
-[EXPR_LIST] SqlNode: 'exprList'
+-[DOT] DotNode: 'stockoptio0_.so_date' {propertyName=soDate,dereferenceType=4,propertyPath=soDate,path=so.soDate,tableAlias=stockoptio0_,className=database.StockOption,classAlias=so}

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
whereas the Hibernate tools HQL editor throws the above error, should I raise a new JIRA for this or will somebody take care of this issue?

I wonder why this issue is open since 2007 and still not resolved or even in progress.
I mean, it should be simple to fix it, right? Creating a function that would be non-database specific, plus it's kind of an important feature.
What's the reason for the low priority?

Thank you!