6 Replies Latest reply on Jun 29, 2016 2:54 PM by gregor

    Processbook Display - vrdate Truncating in GetValue Method


      Hello all,


      I am trying to compare dates using the DateDiff function in order to calculate the run time of a machine.
      I am running into issues with rounding milliseconds though. In order to get the time of an event, I use the "vrdate" value from the "GetValue" method, however the date that is returns appears to be truncating the milliseconds portion of the timestamp. For example, if I right click on the trend that I am pulling data from and select "Show Details and Annotations," it display's the event time as "6/28/2016 12:35:37.97101 PM," however the corresponding vrdate value will be "6/28/2016 12:35:37 PM."


      This ends up causing inaccuracies when I use the DateDiff function with vrdate as it is being truncated.


      I was wondering if there is any way to prevent the vrdate value from truncating and instead have it round up the data to the nearest second.



        • Re: Processbook Display - vrdate Truncating in GetValue Method

          Not sure where the methods that you are using are coming from. It sounds like you have a type conversion issue, the methods that you are using are causing the rounding for the time format, but I would be tempted to treat the dates as floats, then you should see a higher level of accuracy. The number is in seconds so easy enough to convert to minutes ( /60), hours ( /60), days (/24).

            • Re: Processbook Display - vrdate Truncating in GetValue Method

              I tried treating the date value as a float but I don't think the values I was getting made much sense. It seemed to be representing 6/28/2016 1:24:52.96 PM as 42549.516.


              The "vrdate" value I am using is returned by GetValue. For example, in my code I have something like:

              value = trend1.GetValue(vrdate, vrstat)

              The GetValue command here returns the current value of the trend and the date of when that value appeared.

                • Re: Processbook Display - vrdate Truncating in GetValue Method

                  The float value is the number of seconds since 1st Jan 1970. You can take the difference using the float and convert it to a more meaningful run time in hours, days, or whatever you need to see the time difference represented as. Dividing by 86400 would give you the number of days, the decimal part providing the fraction of a day.


                  From what I can make out, you are using ProcessBook with some VBA, but unsure how you are trying to use this to calculate the machine run time. A few more details are required to understand what you are trying to do.

              • Re: Processbook Display - vrdate Truncating in GetValue Method
                John Messinger

                I think the issue here stems from the lack of millisecond precision support in both the Variant Date, and Date types in VBA. The vrdate being returned by the GetValue method is a Date being returned as a Variant type. If you try and explicitly cast it back through CDate(vrdate) you will still be missing the subsecond portion of the timestamp. From my nearly rusty memory of how these behave in VBA, the Date should be rounded to the nearest second rather than simply truncating the subsecond component. Further, the DateDiff function doesn't support millisecond level precision anyway.


                What order of inaccuracy are you seeing in your DateDiff result?

                • Re: Processbook Display - vrdate Truncating in GetValue Method

                  Thanks for the input everyone.


                  Here are a few more details to hopefully help better explain what I am trying to do.


                  I have a trendline that displays the status of a machine (1 indicating running and anything else indicating not running) and I am using the Trend_DataUpdate event in VBA along with GetValue to record when the machine is running and when it is not. When the machine status goes from some value to a 1 (or vice versa), the Trend_DataUpdate event runs and vrdate returns the timestamp of when that change occured. By keeping track of the timestamps, I then use the DateDiff function to calculate how long the machine ran for.


                  The code ends up looking something like this:




                  if "machine status was 1 and now isn't 1" then

                  machine_stat = trend.GetValue(vrdate, vrstat)

                  runtime = DateDiff("s", last_on, vrdate)    'In this case, "last_on" is the time when the machine started running and "vrdate" is when it stopped


                  End Sub


                  I know that DateDiff only supports accuracy by the second so the Date type in VBA not having milliseconds isn't a big issue. This is where the truncating beomces an problem however.

                  If I show the data in Processbook by selecting the trendline and going to "Show annotations and details," the timestamps look something like "6/28/2016 9:12:03.9896 AM."

                  However, this same timestamp in VBA would look like "6/28/2016 9:12:03" as for some reason the vrdate is not rounding to the nearest second.

                  This ends up causing issues where the machine might actually have ran for 6 seconds but VBA will calculate it as only running for 5 seconds. I am collecting the data over 8 hour periods so these 1 second discrepancies begin to add up and distort the results.