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

[Steve Ebersole (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=41986#action_41986 ] 

Steve Ebersole commented on HHH-2434:
-------------------------------------

I'd really prefer a more general solution here along the lines of T-SQL datediff as opposed to adding a new function definition for each and every date part/field.

For everyone complaining about why this has not been addressed, please realize that Dialects are intended to be plugged in to be able to allow you to implement such behavior.

For the person complaining that writing a Dialect is the same as writing custom SQL, well I guess you are entitled to an opinion.  But I totally disagree.

Anyway, the more generic datediff-style solution is a tad more difficult and I am pushing it to Alpha3 and dropping it from 3.6

> No standard way to calculate date intervals in HQL
> --------------------------------------------------
>
>                 Key: HHH-2434
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434
>             Project: Hibernate Core
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 3.2.0.ga
>         Environment: All
>            Reporter: Don Smith
>            Assignee: Steve Ebersole
>            Priority: Minor
>             Fix For: 4.0.0.Alpha3
>
>         Attachments: HHH-2434.patch, hsqldb-stack.txt, IntervalTracker.java, 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