Hi all, how would I do the following T-SQL SELECT using PI SQL
SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON DATEDIFF(SECOND,T1.datetime1,T2.datetime2) BETWEEN 1 AND 10
What PI tables do you want to run your query against using an INNER JOIN? PI SQL supports standard ANSI SQL statements such as JOINs.
If you can provide a bit more information as to what you are looking to do, this will make it easier to provide a useful answer
My apologies for not explaining what I am trying to, I am new to PI, essentially I want to find the difference, in seconds, between 2 dates and return data where the difference is between 1 and 20 seconds.
The PI tables are not important at this point, I merely want to know how I would write the PI SQL for this criteria.
I researched this some more since I posted the original question and I think the following may work (but my server is down at the moment so I can't test it yet):
(CAST((T2.datetime2 – T1.datetime1) AS FLOAT) * 24 * 3600) BETWEEN 1 AND 20
I would really appreciate any help on this as I am new to PI SQL.
There is actually two products that are related to SQL Technologies:
If you look in the manuals ( see the download section) you should find plenty of examples.
Let us know if you have issues finding what you need.
So in answer to the first part of your question - "find the difference, in seconds, between 2 dates", the following works (and you can use PI time formats):
SELECT Cast((Date('31-May-2016 15:43:27') - Date('31-May-2016 15:37:49')) As Float64)
SELECT Cast((Date('*-8m') - Date('*')) As Float64)
For the second part of your question, "return data where the difference is between 1 and 20 seconds", I'm not sure exactly what you mean there - are looking to return data from the archive only where the span between archive values is somewhere between 1 to 20 seconds? Or are you looking at returning a set of evenly spaced samples with a sample rate of say 20 seconds?
Retrieving data ...