| Key: |
HHH-2434
|
| Type: |
Improvement
|
| Status: |
Open
|
| Priority: |
Minor
|
| Assignee: |
Unassigned
|
| Reporter: |
Don Smith
|
| Votes: |
16
|
| Watchers: |
10
|
|
If you were logged in you would be able to see more operations.
|
|
|
Hibernate3
Created: 20/Feb/07 11:02 AM
Updated: 29/Apr/08 11:39 PM
|
|
| Component/s: |
core
|
| Affects Version/s: |
3.2.0.ga
|
| Fix Version/s: |
None
|
|
|
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.
|
|
Description
|
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. |
Show » |
|
No standard way to calculate date intervals in HQL
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.