4 Replies Latest reply on Oct 26, 2015 9:31 AM by Miles Buckley

    Count Number of Values with Timstamp Close to the Hour

    Miles Buckley

      I have noticed that some of the real-time data from our SCADA system is unusually timestamped within one minute of the hour and not spaced out as we would expect.

      Is there a way to count the number of values (per day for example) that have timestamps as described and compare with the total for the same time period.

       

      I've tried downloading the data with datalink but there is just far too much of it and Excel grinds to a halt.

       

      Miles

        • Re: Count Number of Values with Timstamp Close to the Hour
          lsko

          Miles,

           

          Can you describe in more detail how you are expecting the data to be timestamped? If possible, please providing some sample archive values

            • Re: Count Number of Values with Timstamp Close to the Hour
              Miles Buckley

              Lok (or is Ko your first name?), thank you for your request for more information. Hopefully I have given useful information below.

               

              Here are ten recent time stamps for one suspicious tag:

               

              22-Oct-15 11:01:50

              22-Oct-15 10:01:43

              22-Oct-15 09:01:40

              22-Oct-15 08:22:35 ***

              22-Oct-15 08:01:38

              22-Oct-15 06:01:40

              22-Oct-15 05:01:42

              22-Oct-15 04:01:41

              22-Oct-15 03:01:49

              22-Oct-15 02:01:38

               

              All bar the fourth item are time stamped within two minutes of the hour. This should be real time data representing loading on a distribution power transformer which varies throughout the day. The expected timestamps could occur at any time of day and the rate of data flow would vary with time of day. Imagine a sin wave where you would get more values as the wave crosses zero and a slower rate at the top (evening peak demand) and bottom (morning minimum demand) of the wave.

               

              The actual data coming in to PI for this tag (and many others) is not natural and I want to identify those tags affected (out of 23,833) and when the problem began.

               

              COUNT 'tag'

              WHERE MINUTE(timestamp)<=1 OR MINUTE(timestamp)>=58

              BETWEEN '1/4/2014' AND '1/4/2015'

              INTERVAL = '24h'

               

              This would return to Excel a 1x365 array which I could then compare with another derived from.

               

              COUNT 'tag'

              BETWEEN '1/4/2014' AND '1/4/2015'

              INTERVAL = '24h'

               

               

              The PICompDat function may do this but I have no idea where to start with the Filter Expression.

               

              Regards,

               

              Miles

                • Re: Count Number of Values with Timstamp Close to the Hour
                  lsko

                  Miles,

                   

                  Is it normal that there is no timestamp at 7AM? Is that a moment where you expect there to be less data flow? It's difficult to judge without seeing the values. This situation seems a little bit weird to me because if we expect the rate of data flow to change, how can weexpect all data to come through within 2 minutes of the hour? In this case, it looks like we are collecting data at relatively regular intervals.

                   

                  You can enter the following Performance Equation into the Filter Expression: minute('*')<=1 or minute('*')>=58 in a Compressed Data function in PI DataLink. This will retrieve all the archive values with timestamps as you asked for. What I would do is use the filter expression minute('*')>=1 or minute('*')<=58; this way you will find the outlying timestamps directly.

                   

                  One important piece of information to consider here is how the tags are configured. What type of interface is this? Are they set to polled or advised? What is the scan frequency? Is there more data going to the PI Server and getting compressed, etc?

                   

                  If you need more help with Filter Expressions, please take a look at the following video on our YouTube Learning Channel: OSIsoft: Obtain filtered values in PI DataLink. v4.0 - YouTube

                   

                  I go by Lok

                  Lok

                    • Re: Count Number of Values with Timstamp Close to the Hour
                      Miles Buckley

                      Lok, thank you for your guidance. The PE syntax was eluding me. Compressing is off for all tags.

                       

                      Re your earlier point: the data is real time and only when the load on the network changes does a new value end up in PI. The data rate to PI reflects the rate that domestic, commercial and industrial consumers change their energy usage.

                       

                      I had noticed in ProcessBook that (for a few tags) data was changing ONLY near the hour. This is where I got suspicious and started to investigate. Your assistance will allow be to report the tags affected, and when the issue began, so that the SCADA team can investigate further.

                       

                      Thank you,

                       

                      Miles