I would like to know where can i find the PI SQL Query document reference user guide?
There is a file named PISQLComander in ..\PIPC\HELP folder.
You want the reference to the supported syntax and functions for writing SQL query against PI OLEDB Classic Provider or PI OLEDB Enterprise. You can take a look inside the respective user manual:
Hi! Han Yong,
Thank you for your quick reply. :)
If I query the PI data via PI JDBC 2012 method (java code), can i still refer to the above mentioned documentation for the pl sql query syntax?
e.g. how to manipulate the timestep or time column fields in the where clause criteria.
As I'm a newbie for PI SQL query and I want to explore into PI SQL Qeury area but i don't know where to start to learn it because the PI SQL query syntax is very different than those standard ANSI SQL (e.g oracle sql).
I'm really appreciated you help.
Yes, PI JDBC Driver is actually a Type 1 JDBC Driver that utilizes a WCF Service (PI SQL Data Access Server) which in turn uses PI OLEDB Provider or PI OLEDB Enterprise to service the SQL queries from the Java application. Hence the SQL syntax supported by PI JDBC is the same.
The sample queries that comes with PI SQL Commander will probably serve as a good starting point for you.
Hi Han Yong,
Again, thanks for your answer.
We are writing an adapter to retrieve data from PI into our own system. Up until now we have connected to PI OLEDB, and run queries directly against this. Here we are able to specify that we want to use UTC as our timezone, and all our requests and what we get back from PI are UTC timestamps.
With the JDBC driver, I understand that we can get the UTC timestamp of the samples we retrieve by fetching the __utctime. However, in our where clause we have to use time as the field when we specify our time range. Is it possible to ask for data using utc timestamps, and retrieve data using utc timestamps through the jdbc adapter, or is this not supported?
With PI OLEDB Provider, there is an option to tell the provider to process time in local or UTC.
Unfortunately, PI JDBC Driver has a limitation that it doesn't support this option. I guess the only alternative is to explicitly convert the datetime parameter from your Java app to local time before querying the PI JDBC Driver with the parameter.
If one is going to ask using local time, how does the driver distinguish between the two equal hours during the summer to winter change?
Well, unfortunately I am not located in a region with DST and I am not aware how to specify the time during DST transition hours. Let me check with the PI JDBC developer and see if he has any suggestions on this.
I don't understand the word "unfortunately" in your reply, Han Yong ;)
We are happy that our software does not crash any more twice a year, our governments put the DST transition to a time when it usually bothers least,
but in about half a year from now we will have two hours that are hard to distinguish from each other.
Additionally, Java is pretty clear about timestamps, and applies localization and DST offsets only when displaying them, but OLEDB does this already under the hood.
PI JDBC driver talks to PI SQL DAS, and there's currently no way to get the time zone of that node. So we document the restriction that the JDBC driver should run in the same time zone as the DataAccessServer, which permits the PI JDBC driver to calculate UTC from a timestamp in (DAS) local time, which is provided by PI OLEDB provider .
We found that requesting UTC timestamps usually produces more problems, as it would mean that all input timestamps in queries and paramters ( including hard coded dates and PI Time Format like 'T' ) would be interpreted as UTC as well.
The hidden column __utctime is not recognized as a special case, nor added secretly to queries by the PI JDBC driver. So it could just be used by your own logic.
Another approach to distinguish the one duplicate hour per year, might be to have the PI OLEDB provider supply a time difference ( time - 'T' ) since midnight. If PI OLEDB produces this difference, it is not affected by time gaps in spring and remains unique during the transition hour in autumn.
SELECT tag, time, time - 'T' "TimeOnly", cast (time-'T' as float64)/3600. "TimeNumber", value
WHERE tag = 'sinusoid' AND time BETWEEN 'T' AND '*'
Or, a suggestion from my colleage: Make sure there are enough events in that time to deduct from the sequence of events, which is which.
Michael HesselbachI don't understand the word "unfortunately" in your reply, Han Yong ;)
Just means that I don't deal with DST enough to really know what to suggest for this
Hi. Found this while researching Pi JDBC and SQL Queries, and want to make sure I understand the details here. We've made a decision to migrate our application to use Pi as the primary system for real time series data. We're currently planning to update some of our core technology, much of which is written in java communicating with a postgresql DBMS. So we're looking at the different Pi technologies for accessing data, including JDBC. Our system manages data from multiple time zones. Some use DST and some don't. Currently, we require that all time data in all DBs make it clear what timezone the timestamp is, with a strong preference for using UTC everywhere except final presentation to a user. (The only exception is schedule data, which is still in target control device local time). We further require that all production machines be set to UTC as their local time zone.
Michael, are you saying that timestamps from Pi are handled as follows?
Given the following call via Pi JDBC:
SELECT tag, time, value FROM pisnapshot
WHERE tag like '%SINU%' AND time between '*-5m' AND '*';
Data Flow and timestamp manipulation:
1. PI SDK retrieves Pi data from DB with a UTC timestamp
2. PI OLEDB converts timestamp to machine local time for the calling machine.
3. PI SQL DAS makes no changes to the timestamp
4. PI JDBC makes no changes to the timestamp
Yes. With one repeated (?) remark:
PI JDBC does not know PI SQL DAS timezone and assumes it is the same as its own.
To get a valid Java time, it eventually needs to convert the supplied time back to UTC (java stores in UTC and only translates to local time on output demand).
If the application (JVM) running the PI JDBC driver runs in UTC as the default time zone, and PISQLDAS is in UTC as well,your "4. PI JDBC makes no changes to the timestamp" is correct.
Your "2. PI OLEDB converts timestamp to machine local time for the calling machine." might benefit from the comment that "the calling machine" is PISQLDAS.
Take care: Any timestamp literals, like "T", "1-May-2013 00:00:00" refer to the time zone of PISQLDAS (UTC) as well.
Retrieving data ...