[teiid-issues] [JBoss JIRA] (TEIID-2692) Incorrect results with TIMESTAMPDIFF for months
[
https://issues.jboss.org/browse/TEIID-2692?page=com.atlassian.jira.plugin...
]
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