Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5097

Cannot run time-based queries against Osisoft PI

    XMLWordPrintable

    Details

    • Type: Quality Risk
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 8.12.11.6_4
    • Fix Version/s: 10.1
    • Component/s: JDBC Connector
    • Labels:
      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'
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                asmigala Andrej Šmigala
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: