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

[Hibernate-JIRA] Created:...

Don Smith (JIRA)

Tuesday, 20 February 2007 Tue, 20 Feb '07

6:02 p.m.

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 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

Show replies by date

Steve Ebersole (JIRA)

Wednesday, 21 February Wed, 21 Feb

3:37 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Steve Ebersole commented on HHH-2434: ------------------------------------- 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.

...

Components: core Versions: 3.2.0.ga Environment: All Reporter: Don Smith Priority: Minor

...

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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

Don Smith (JIRA)

Friday, 23 February Fri, 23 Feb

10:42 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] 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)

...

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.

Don Smith (JIRA)

10:42 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=all ] Don Smith updated HHH-2434: --------------------------- Attachment: IntervalTracker.java

...

Don Smith (JIRA)

10:42 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=all ] Don Smith updated HHH-2434: --------------------------- Attachment: hsqldb-stack.txt HSQLDB stack trace

...

Don Smith (JIRA)

10:42 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=all ] Don Smith updated HHH-2434: --------------------------- Attachment: postgresql-stack.txt PostgreSQL stack trace

...

Don Smith (JIRA)

Monday, 5 March Mon, 5 Mar

10:27 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=all ] Don Smith updated HHH-2434: --------------------------- Attachment: HHH-2434.patch Attaching a patch containing function defintions for "seconds_between" and "milliseconds_between" for the following dialects: DB2Dialect DerbyDialect HSQLDialect MySQL5Dialect Oracle9Dialect PostgreSQLDialect SQLServerDialect SybaseDialect

...

Components: core Versions: 3.2.0.ga Environment: All Reporter: Don Smith Priority: Minor Attachments: HHH-2434.patch, 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.

Paul Benedict (JIRA)

Wednesday, 30 April Wed, 30 Apr

6:36 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Paul Benedict commented on HHH-2434: ------------------------------------ The patches look great. Why aren't these being considered for 3.3?

...

No standard way to calculate date intervals in HQL -------------------------------------------------- Key: HHH-2434 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434 Project: Hibernate3 Issue Type: Improvement Components: core Affects Versions: 3.2.0.ga Environment: All Reporter: Don Smith Priority: Minor 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.

radhakrishna (JIRA)

Friday, 6 March Fri, 6 Mar

12:16 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] radhakrishna commented on HHH-2434: ----------------------------------- 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} | +-[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} 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

...

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 Priority: Minor 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.

radhakrishna (JIRA)

12:48 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] radhakrishna commented on HHH-2434: ----------------------------------- 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?

...

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 Priority: Minor 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.

Luiz Ribeiro (JIRA)

Friday, 5 June Fri, 5 Jun

9:06 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Luiz Ribeiro commented on HHH-2434: ----------------------------------- 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!

...

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 Priority: Minor 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.

Dmitry Katsubo (JIRA)

Wednesday, 23 February Wed, 23 Feb

3:19 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Dmitry Katsubo commented on HHH-2434: ------------------------------------- I would be happy to see days_between() function in HQL (for example, HSQL does not support "DATETIME - DATETIME >= NUMBER" syntax).

...

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 Priority: Minor 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.

Steve Ebersole (JIRA)

Friday, 1 April Fri, 1 Apr

5:03 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Steve Ebersole updated HHH-2434: -------------------------------- Assignee: Steve Ebersole Fix Version/s: 4.0.0.Alpha2 3.6.3

...

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: 3.6.3, 4.0.0.Alpha2 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.

Steve Ebersole (JIRA)

Wednesday, 6 April Wed, 6 Apr

3:06 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Steve Ebersole updated HHH-2434: -------------------------------- Fix Version/s: (was: 3.6.3) (was: 4.0.0.Alpha2) 4.0.0.Alpha3

...

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.

Steve Ebersole (JIRA)

3:06 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] 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.

Steve Ebersole (JIRA)

Tuesday, 3 May Tue, 3 May

9:58 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Steve Ebersole updated HHH-2434: -------------------------------- Fix Version/s: (was: 4.0.0.Alpha3) 4.1.0

...

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.1.0 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.

Steve Ebersole (JIRA)

Tuesday, 27 December Tue, 27 Dec

7:06 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-2434) No standard way to calculate date intervals in HQL

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434?page=c... ] Steve Ebersole updated HHH-2434: -------------------------------- Fix Version/s: (was: 4.1.0) 5.0.0

...

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: 5.0.0 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. For more information on JIRA, see: http://www.atlassian.com/software/jira

4510

days inactive

6281

days old

hibernate-issues@lists.jboss.org

Manage subscription

16 comments

6 participants

tags (0)

participants (6)

Dmitry Katsubo (JIRA)
Don Smith (JIRA)
Luiz Ribeiro (JIRA)
Paul Benedict (JIRA)
radhakrishna (JIRA)
Steve Ebersole (JIRA)

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

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