[teiid-issues] [JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
Ramesh Reddy (JIRA)
issues at jboss.org
Wed Oct 11 11:54:00 EDT 2017
[ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13475898#comment-13475898 ]
Ramesh Reddy edited comment on TEIID-5097 at 10/11/17 11:53 AM:
----------------------------------------------------------------
[~asmigala] You should be issuing queries in Teiid dialect like
{code}
select * from dvqe.Data.Archive a where a.time BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -14, now()) AND now()
{code}
I do not expect Teiid understanding Pi semantics of time notation unless it ANSI SQL format. There may be a way to error out, I am not sure. [~shawkins] might know.
was (Author: rareddy):
[~asmigala] You should be issuing queries in Teiid dialect like
{code}
select * from dvqe.Data.Archive a where a.time BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -14, now()) AND now()
{code}
> Cannot run time-based queries against Osisoft PI
> ------------------------------------------------
>
> Key: TEIID-5097
> URL: https://issues.jboss.org/browse/TEIID-5097
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
>
> 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}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list