Cannot run time-based queries against Osisoft PI ------------------------------------------------ Key: TEIID-5097 URL: https://issues.jboss.org/browse/TEIID-5097 Project: Teiid Issue Type: Quality Risk Components: JDBC Connector Affects Versions: 8.12.x-6.4 Reporter: Andrej Šmigala Assignee: Steven Hawkins Osisoft PI supports a relative time literals syntax, e.g. {code:sql} select * from dvqe.Data.Archive a where a.time between '*-14d' and '*' {code} will select all data between right now and 14 days ago, and {code:sql} select * from dvqe.Data.Archive a where a.time > 'y' {code} will select all data after yesterday midnight. The string literals are converted to time values in the PI Server Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource: {code:sql|title=Pushed query} SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0. [ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y' {code}