Filter of timestamp field pushdown

[haifen_bi]

Hi,

We created a view from a data source (GE Proficy) using Teiid Designer.  One of the fields is timestamp. The underline data source has large amount of historian data, so filtering by timestamp filed is required.

When following query is executed through Teiid:

select * from WAAV where wins = 'H977401' and TimeStamp > '2015-11-02 00:00:00.0'

It was timed out because timestamp filed was not pushed down to the source due to convert function was not found:

2015-11-03 17:07:17,778 DEBUG [org.teiid.PLANNER] (Worker0_QueryProcessorQueue0) [QueryAnnotation<Relational Planner, LOW,convert function not supported by source ProficyODBCSrc,convert(WAAV."TimeStamp", string) was not pushed>]

We tried direct query to source using ODBC-JDBC bridge, with same SQL:

select * from ProficyODBCSrc where WAAV.wins = 'H977401' and TimeStamp > '2015-11-02 00:00:00'

It returned correct data  so I guess conversion from String to timestamp is supported in the source.

Do you have any suggestions as how to solve this issue? Any help is much appreciated.

Thank

Haifen

[shawkins]

> Do you have any suggestions as how to solve this issue? Any help is much appreciated.

What version are you on?  I would expect '2015-11-02 00:00:00.0' to be converted automatically to timestamp since it matches the expected format.  Are there other circumstances where you are using alternative timestamp formats?

[haifen_bi]

Thanks for the quick response.

We asked to double check the SQL that caused timestamp filter not pushing down to source, it actually was:

select * from WAAV where wins = 'H977401' and TimeStamp > '2015-11-02'

After correcting the format, timestamp filter was pushed down and query returned correct data.

Thank you for the help!

[shawkins]

The issue of using a date string literal is addressed by [TEIID-3722] Add an option to not widen comparisons to string - JBoss Issue Tracker - which will be the default behavior for Teiid 9.