Details
-
Quality Risk
-
Resolution: Done
-
Major
-
8.12.11.6_4
-
None
Description
Osisoft PI supports a relative time literals syntax, e.g.
select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
will select all data between right now and 14 days ago, and
select * from dvqe.Data.Archive a where a.time > 'y'
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:
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'
