[teiid-issues] [JBoss JIRA] (TEIID-2692) Incorrect results with TIMESTAMPDIFF for months

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2692?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12812676#comment-12812676 ] 

Steven Hawkins commented on TEIID-2692:
---------------------------------------

Duplicate of TEIID-2422

Given the lack of consistency among vendors with TIMESTAMPDIFF there isn't necessarily a single correct answer here.  TEIID-2422 modifies the logic to compute the diff based upon calendar fields rather than canonical time span differences - with a system property to preserve the old behavior.  If desired for a backport, the property should be defaulted as false to preserve the old behavior as to not introduce a behavioral change in a patch.
                
> Incorrect results with TIMESTAMPDIFF for months
> -----------------------------------------------
>
>                 Key: TEIID-2692
>                 URL: https://issues.jboss.org/browse/TEIID-2692
>             Project: Teiid
>          Issue Type: Bug
>          Components: Server
>    Affects Versions: 7.7.7
>         Environment: EDS  + roll up patch 3
>            Reporter: Adam Kovari
>            Assignee: Steven Hawkins
>
> The TimestampDiff counts months incorrectly:
> {code}
> select TimestampDiff(SQL_TSI_MONTH, {d '2010-01-01'},{d '2015-01-01'}) --60 
> select TimestampDiff(SQL_TSI_MONTH, {d '2010-01-01'},{d '2016-01-01'}) --73 however the diff is 72(adding extra 1 month in 6th year)
> select TimestampDiff(SQL_TSI_MONTH, {d '2010-01-01'},{d '2017-01-01'}) --85 
> select TimestampDiff(SQL_TSI_MONTH, {d '2010-01-01'},{d '2022-01-01'}) --146 however the dff is 144 (adding extra 2 in 12 year)
> {code}
> {code} 
> Year	Month	Expect	Teidd TimeStampDiff	   
> 5	12	60	60	   
> 6	12	72	73	   
> 10	12	120	121	   
> 11	12	132	133	   
> 12	12	144	146	   
> 13	12	156	158	   
> 14	12	168	170	   
> 15	12	180	182	   
> 16	12	192	194	   
> 17	12	204	206	   
> 18	12	216	219
> {code}

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira