7 Replies Latest reply on Oct 3, 2015 8:51 AM by Bas Piepers

    Prevent to_char implicit conversion for TimeStamp field in database

    Bas Piepers Novice

      I'm currently toying with the behavior of the translator for a specific TimeStamp field in my model in a Teiid 8.4/JDV 6.0 setup. The database stores this field as a TimeStamp type but very often, clients that connect to the VDB query that field with a string literal like:


      SELECT (...) FROM [table_name] WHERE [date_field] > '2015-09-01' AND [date_field] < '2015-09-10';


      Unfortunately, when you issue a query like this it is in the end translated like:


      SELECT (...) FROM [table_name] WHERE to_char(g_2."[date_field]", 'YYYY-MM-DD HH24:MI:SS.MS') > '2015-03-01' AND to_char(g_2."date_field", 'YYYY-MM-DD HH24:MI:SS.MS') < '2015-04-01'


      This is a Netezza database with the corresponding Netezza translator set on the data source. Converting a timestamp to a Varchar like this is very expensive (takes minutes to complete) so I would rather have it pushed down to the database as is (which would in our scenario decrease the processing time to seconds), so without the to_char call. Only when I add time to the above statement like so:

      SELECT (...) FROM [table_name] WHERE [date_field] > '2015-09-01 00:00:00' AND [date_field] < '2015-09-10 00:00:00';


      The engine translates it to an acceptable form:


      SELECT (...) FROM [table_name] WHERE [date_field] > {ts '2015-09-01 00:00:00.0'} AND [date_field] < {ts '2015-09-10 00:00:00.0'};


      But unfortunately, clients just don't issue statements like that. They often omit the timestamp (but should be able to provide it if needed).


      My question is: how can I prevent JDV from trying to do implicit conversion? As the Reference Material states (paragraph 4.3) JDV automatically converts string literals to a date type but in my case it is trying to convert it to a Varchar in cases where no time is added to the literal. This is not what we want.