[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2434) No standard way to calculate date intervals in HQL

[Don Smith (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=comments#action_26273 ] 

Don Smith commented on HHH-2434:
--------------------------------

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)


> No standard way to calculate date intervals in HQL
> --------------------------------------------------
>
>          Key: HHH-2434
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434
>      Project: Hibernate3
>         Type: Improvement

>   Components: core
>     Versions: 3.2.0.ga
>  Environment: All
>     Reporter: Don Smith
>     Priority: Minor
>  Attachments: IntervalTracker.java, hsqldb-stack.txt, postgresql-stack.txt
>
>
> Date interval calculation is supported differently on different database platforms. Some allow direct arithmetic on columns, i.e. enddate - startdate. Some require functions, datediff(), timestampdiff(), etc. This causes cross-platform issues. For instance, an application I work on has to figure out the dialect that's in use (out of the four we currently support) and create the HQL string differently for each platform. This is undesirable, since we use Hibernate to enable platform neutrality; our installer asks which database the customer wants to deploy to, and sets the dialect. We'd like our codebase to be free of dialect-specific code.
> I propose a standard solution for this, either direct date arithmetic, or a function defintion that is ported across dialects. Timestampdiff seems to be a fairly standard function, although DB2 has different syntax than MySQL and Derby. I've seen hints that timestampdiff is part of the ANSI SQL standard, but do not have access to the documents to determine if that is the case.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira