Date Format for a SQL (OSISOFT PI)

[#1]

Hi,

after some trying i was able to get data from our PI Archive. We use PI (Osisoft) to archive our process data.

With the following query I got the data but the timestamp is not there:

SELECT t1.tag, t1.time, t1.value, t1.svalue,

t1.status, t1.flags

FROM picomp t1

WHERE ( ( ( t1.tag = 'AL2_Geschwindigkeit_Linienmotor_Formerstation' ) AND ( (t1.time >= ( '2017/03/05 1:47:16 PM' )) AND (t1.time <= ( '2017/03/05 2:44:40 PM' )) ) ) )

The column for the timestamp is just empty (numeric, continous).

Then I had the idea that the column for the timestamp is not numeric. I tried to convert the time into date with the follwoing query:

SELECT t1.tag, TO_Date(SUBSTR (time, 1, 15),'yyyymmdd') time, t1.value, t1.svalue,

t1.status, t1.flags

FROM picomp t1

WHERE ( ( ( t1.tag = 'AL2_Geschwindigkeit_Linienmotor_Formerstation' ) AND ( (t1.time >= ( '2017/03/05 1:47:16 PM' )) AND (t1.time <= ( '2017/03/05 2:44:40 PM' )) ) ) )

But there i got the error:

[PIODBC] [PIOLEDB] [SUBSTR] Overload resolution failed for (DateTime, Int16, Int16) arguments(s). [SQLSTATE=HY000]

What did I wrong?

Can anybody help me?

[#2]

A few questions here: are you trying to code the SQL manually or use Query Builder? Also what version of JMP are you using?

Thanks!

M

[#3]

I use JMP Pro 13.1.

I start with the query Builder. But I have to switch to "Custom SQL". Because the Query from the Query Builder does not work. For eample I have to delete the " ; " at the end of the query. And there are some other things. Thats annoying but manageable.

[#4]

Under the follwoing link you will find an example for this " ; " -Problem from Osisoft.

https://techsupport.osisoft.com/Troubleshooting/KB/KB01377

[#5]

I think the ";" issue got fixed in 13. You might want to give it a try again. My standard advice for SQL is to let Query Builder do the coding. If you want to embed the SQL in a JSL application or something you can always pull it out after the fact - but the QB do the heavy lifting so you know it works.

Best,

M

[#6]

The ";" issue is still there. But that is not my problem. I can delete it easyly.

My Problem is that I do not get the time. It seems to be that JMP converts the time data to numeric. But in the PI Archive it seems to be a string or something relative. If I use Microsoft Access I get the time. Access tells me that the data type is "Text". Maybe I will ask OSISOFT directly how I can get the time by a SQL.

[#7 · Accepted solution]

Ok...now it works with the following SQL

SELECT t1.tag, Cast (time as string) STRING_time, t1.value, t1.svalue,

t1.status, t1.flags

FROM picomp t1

WHERE ( ( ( t1.tag = 'AL2_Geschwindigkeit_Linienmotor_Formerstation' ) AND ( (t1.time >= ( '2017/03/05 1:47:16 PM' )) AND (t1.time <= ( '2017/03/05 2:44:40 PM' )) ) ) )

[#8]

I know this is an old post, but check out the Aveva/OSISoft PI Tools Add-in, it can help write these queries for you.