[teiid-issues] [JBoss JIRA] (TEIID-2422) Offer support for a timestampdiff based upon calendar fields

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-2422?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-2422.
-----------------------------------

    Fix Version/s: 8.3
       Resolution: Done


Switched timestampdiff to compute differences based upon calendar fields following sql server behavior as closely as possible - documented in the Reference. 

There is a switch to use the old Teiid behavior which is in the Admin Guide and mentioned in the release notes. 

We are still returning a long from of timestampdiff function (the same as 8.2) rather than an integer, but almost all sources will likely throw an exception if the result is out of the integer range. 

With this change however the pushdown to mysql, h2, and mm of timestamp diff may be the wrong result for intervals greater than seconds, thus pushdown of the system timestampdiff was disabled and support for source specific pushdown was added - mysql.timestampdiff etc. where the interval is specified as a literal instead of a keyword. It is a potential compatibility issue if someone wants to use the system timestampdiff and expects pushdown to mysql/h2/mm - but they can always just extend the translator to add timestampdiff to the supported function list. 

I did not exhaustively validate all of the other timestampdiff implementations, so there may be other pushdown inconsistencies lurking. The approach would be similar there to disable the system support and add a pushdown function. It may also be desirable eventually to denote which intervals are supported or add compensating logic (such as has been done for fractional seconds) to account for differences in the month/week etc. calculations.
                
> Offer support for a timestampdiff based upon calendar fields
> ------------------------------------------------------------
>
>                 Key: TEIID-2422
>                 URL: https://issues.jboss.org/browse/TEIID-2422
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 8.3
>
>
> There is little consistency among timestampdiff implementations, however ours seems to be based off of the db2 (http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.sqlref/src/tpc/db2z_bif_timestampdiff.htm) timestampdiff in terms of intervals - but does not make all the same assumptions in the calculation of the answer.
> It would be good to provide an option so that our timestampdiff or another system function would calculate differences based upon calendar fields (following the behavior of SQL Server) rather than just based upon the interval - for example the months between 2012-02-20 and 2012-03-01 would report 1 rather than the current answer of 0).
> There is also a general issue with the consistency of the results with the pushdowned versions of timestampdiff as vendor support varies.

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