What is the data type in teiid for timeStep?  If it is a date/time type that literal comparison is not valid.  If that is the case see [TEIID-5097] Cannot run time-based queries against Osisoft PI - JBoss Issue Tracker

To workaround you need to wrap the literal in a function that will the direct comparison in the generated source query.

Yes that is correct.

> But how do we convert like '2d' into valid time via push down function?

Teiid 10.1+ adds a pushdown function on the pi schema:  create function interval(param string) returns timestamp options ("teiid_rel:native-query" $1)

If you are using an older Teiid version, you can add a similar function on the source schema either via DDL or Designer.

Then you don't need to convert an interval literal, but instead use the function:

pi.timeStep = pi.interval('1d')

I have included the VDB xml

<model name="PI" type="PHYSICAL" visible="true">     

          <property name="importer.importProcedures" value="true"/>

          <property name="importer.tableTypes" value="TABLE"></property>

           <property name="importer.importKeys" value="false"></property>

            <source translator-name="osisoft-pi" connection-jndi-name="java:/PISrc" name="PISrc" />

           <metadata type="NATIVE"/>

            <metadata type="DDL">

            <![CDATA[

                    CREATE FOREIGN FUNCTION interval1 (arg1 string) RETURNS timestamp OPTIONS ("teiid_rel:native-query" '$1');

            ]]>

        </metadata>

    </model>  

I am executing the select statement on the Squirrel,

SELECT pi."Time", pi."value", pi.tag from "PiArchive"."PiAvg" pi where

pi.tag= 'CDT158' and

pi.timestep = pi.interval1('5d') AND

pi.time BETWEEN '2018-01-27'  AND '2018-01-28'

I got an error below,

Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PISrc: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT TOP 100 g_0.[time] AS c_0, g_0.[value] AS c_1, g_0.[tag] AS c_2 FROM [piarchive].[piavg] AS g_0 WHERE g_0.[tag] = 'CDT158' AND cast(g_0.[timestep] AS DateTime) = '5d' AND g_0.[time] >= '2018-01-27 00:00:00.0' AND g_0.[time] <= '2018-01-28 00:00:00.0']

SQLState:  50000

ErrorCode: 30504

Please advise me.

Its working fine from the below code changes in VDB.xml

<metadata type="NATIVE"/> 

<metadata type="DDL"> 

            <![CDATA[

CREATE FOREIGN FUNCTION TIMEINTERVAL(arg1 string) RETURNS TIME OPTIONS ("teiid_rel:native-query" '$1');

]]> 

</metadata>

Squirrel select statement,

SELECT pi.Tag, pi."Time", pi."value" FROM "PiArchive"."PiAvg" pi

WHERE pi.tag= 'CDT158' AND

pi.timestep = TIMEINTERVAL('1w')  AND

pi.time BETWEEN '2018-01-01' AND '2018-12-31'

Metadata elements in the DDL are chained. When you define just the DDL one, then the model is only populated with metadata that is defined. Adding "NATIVE" brings in the metadata from the source about your schema. NATIVE is the default, but only when no other metadata type is defined.