AnsweredAssumed Answered

Subtracting time in PI SQL Commander, why is it not working?

Question asked by jeff_denz on Apr 17, 2015
Latest reply on Apr 17, 2015 by Eugene Lee

I'm developing a query using PI SQL Commander.  I need to be able to subtract 5 minutes from a time to be used as an input to a performance equation. 

The below query works if I wanted to add 5 minutes to a time:

 

SELECT c2.time + '5m'

FROM(

  SELECT c1.tag, c1.time, c1.value, PrevVal('inserttagname',c1.time) as Previous

  FROM(

  SELECT tag, time, value

  FROM piarchive.picomp2

  WHERE tag = 'inserttagname' and value = 0 and time between '1-JAN-2015' and '*') as c1

  WHERE c1.value <> PrevVal('inserttagname',c1.time)) as c2

where c2.previous = 1

 

But what I really want to do is subtract 5 mins from the time which doesn't work (I get null results):

SELECT c2.time - '5m'

FROM(

  SELECT c1.tag, c1.time, c1.value, PrevVal('inserttagname',c1.time) as Previous

  FROM(

  SELECT tag, time, value

  FROM piarchive.picomp2

  WHERE tag = 'inserttagname' and value = 0 and time between '1-JAN-2015' and '*') as c1

  WHERE c1.value <> PrevVal('inserttagname',c1.time)) as c2

where c2.previous = 1

 

I have also tried:

SELECT c2.time + '-5m'

FROM(

  SELECT c1.tag, c1.time, c1.value, PrevVal('inserttagname',c1.time) as Previous

  FROM(

  SELECT tag, time, value

  FROM piarchive.picomp2

  WHERE tag = 'inserttagname' and value = 0 and time between '1-JAN-2015' and '*') as c1

  WHERE c1.value <> PrevVal('inserttagname',c1.time)) as c2

where c2.previous = 1

Outcomes