Interpolated Calculation Modes

Interpolation is used in many calculation modes. When using interpolated data, it is possible that there are no raw samples in the interval (such as with highly-compressed data) so the archiver requires additional samples to perform calculations.

The Minimum, MinimumTime, Maximum, and MaximumTime all use interpolation to arrive at two additional samples per interval. One is interpolated at the interval start time and one is interpolated at the interval end time. The interpolated samples are used in calculations just like raw, collected samples within the interval. In particular, the minimum or maximum calculated value can be a raw or interpolated value.

All described rules for interpolating a value at an interval's end time also apply to the interval's start time. There is no raw maximum or raw minimum sampling mode. To acquire these values, you must retrieve the raw samples using RawByTime or RawByNumber and compute the minimum or maximum yourself.

Similarly, you must also manually calculate a minimum or maximum when using values acquired through lab sampling.

Minimum/Maximum and MinimumTime/MaximumTime Modes: The minimum (or maximum) value in the interval and the time stamp of that value.
  • Value:

    Maximum returns the raw or interpolated value with the greatest value and good data quality in the interval. Minimum returns the raw or interpolated value with the lowest value and good data quality in the interval

    MaximumTime returns the time stamp of the Maximum value. MinimumTime returns the time stamp of the Minimum value.

    In all cases, all raw samples of bad quality is ignored, both during interpolation and when calculating the maximum.

  • Quality: If the raw samples in the interval all have bad quality, or if the sample before the interval has bad quality, then percent good is 0. Otherwise, percent good is always 100, even if the interval does not contain any raw samples or contains both good and bad quality samples.

TimeGood Mode: The TimeGood mode calculates the amount of time for which the data was of good quality.

The TimeGood mode is most useful when combined with filtered data queries. You can use a filter condition to acquire samples for which a specific condition was true, then calculate for how long that data was of a good quality. For example, you could use a filter condition to determine the amount of time a pump was activated, then calculate for how much of that time the data was of a good quality.

To get the most use out of the TimeGood mode, you should understand how filtered data queries work.

  • Value: The TimeGood mode retrieves the total number of milliseconds during the interval for which the data is good AND for which the filter condition is true. If there is no filter tag or condition, then TimeGood is the total number of milliseconds in the interval that the data is good.
  • Quality: The TimeGood mode always has a percent good of 100, even if there are no raw samples or if all samples have bad quality. In the latter case, the Value will be 0, but the percent good is still 100.

Finding minimum and maximum of Downward Sloping Data

The following example demonstrates how a raw sample is interpolated at the interval's start and end time and how this interpolation is used with raw samples when calculating minimum and maximum values.

Import the following data: 
[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
DOWNSLOPE,SingleFloat,100,0
[Data]
Tagname,TimeStamp,Value,DataQuality
DOWNSLOPE,29-Mar-2002 13:59:00.000,22,Good
DOWNSLOPE,29-Mar-2002 14:08:00.000,12,Good
DOWNSLOPE,29-Mar-2002 14:22:00.000,4,Good
The following query retrieves the Maximum value: 
select timestamp, value, quality from ihrawdata where samplingmode=calculated 
and calculationmode=Maximum and timestamp >= '29-Mar-2002 13:50' and 
timestamp <= '29-Mar-2002 14:30' and tagname  = DOWNSLOPE and numberofsamples = 8
The following query retrieves the MaximumTime:
select timestamp, value, quality from ihrawdata where samplingmode=calculated 
and calculationmode=MaximumTime and timestamp >= '29-Mar-2002 13:50' and 
timestamp <= '29-Mar-2002 14:30' and tagname  = DOWNSLOPE and numberofsamples = 8
The following query retrieves the Minimum:
select timestamp, value, quality from ihrawdata where samplingmode=calculated 
and calculationmode=Minimum and timestamp >= '29-Mar-2002 13:50' and 
timestamp <= '29-Mar-2002 14:30' and tagname  = DOWNSLOPE and numberofsamples = 8
The following query retrieves the MaximumTime value: 
select timestamp, value, quality from ihrawdata where samplingmode=calculated 
and calculationmode=MinimumTime and timestamp >= '29-Mar-2002 13:50' and 
timestamp <= '29-Mar-2002 14:30' and tagname  = DOWNSLOPE and numberofsamples = 8
The following query retrieves the Minimum value: 
select timestamp, value, quality from ihrawdata where samplingmode=calculated 
and calculationmode=Min
The following query retrieves the MinimumTime value:
select timestamp, value, quality from ihrawdata where samplingmode=calculated 
and calculationmode=MinimumTime and timestamp >= '29-Mar-2002 13:50' and 
timestamp <= '29-Mar-2002 14:30' and tagname  = DOWNSLOPE and numberofsamples = 8
Interval Time Stamp Maximum Maximum Time Minimum Minimum Time Quality
13:55:00.000 0.00 31-Dec-1969 19:00:00.000 0.00 31-Dec-1969 19:00:00.000 0.00
14:00:00.000 22.00 13:59:00.000 20.89 14:00:00.000 100.00
14:05:00.000 20.89 14:00:00.000 15.33 14:05:00.000 100.00
14:10:00.000 15.33 14:05:00.000 10.86 14:10:00.000 100.00
14:15:00.000 10.86 14:10:00.000 8.00 14:15:00.000 100.00
14:20:00.000 8.00 14:15:00.000 5.14 14:20:00.000 100.00
14:25:00.000 5.14 14:20:00.000 4.00 14:25:00.000 100.00
14:30:00.000 4.00 14:30:00.000 4.00 14:30:00.000 100.00

The value is 4 for the entire interval of 14:26:00 to 14:30:00. However, the newest value is always returned for MinimumTime and MaximumTime for an interval, so the values instead are calculated as 14:30.

All modes have the same quality. A MaximumTime or MinimumTime of 1969 means there is no value in that interval.

Maximum always begins at the start of the interval because the data forms this is a downwards-sloping line. The Maximum takes the sample interpolated at the interval start time. The timestamp is still the interval end time.

When an interval has no raw samples, such as in the 14:05 interval, samples are interpolated at the beginning and the end of the interval. This means that the 14:05 interval has 2 samples to example at when calculating the Minimum or Maximum.

Finding Minimum and Maximum of Changing Data

The following example uses a value that continually changes, rather than one that simply slopes upwards or downwards. Any Minimum or Maximum within an interval is necessarily a raw sample. If the minimum or maximum occurred as raw samples in the middle of the interval, these are also detected.

Import the following data: 

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
SAWTOOTH,SingleFloat,60,0
 
[Data]
Tagname,TimeStamp,Value,DataQuality
SAWTOOTH,29-Mar-2002 13:59:00.000,22.7,Good
SAWTOOTH,29-Mar-2002 14:01:00.000,12.5,Good
SAWTOOTH,29-Mar-2002 14:02:00.000,47.0,Good
SAWTOOTH,29-Mar-2002 14:03:00.000,2.4,Good
SAWTOOTH,29-Mar-2002 14:04:00.000,9.5,Good
SAWTOOTH,29-Mar-2002 14:08:00.000,12.5,Good
SAWTOOTH,29-Mar-2002 14:14:00.000,7.0,Good
SAWTOOTH,29-Mar-2002 14:22:00.000,4.8,Good
The following query retrieves the Maximum value:
select timestamp, value, quality from ihrawdata where 
samplingmode=calculated and calculationmode=Maximum and timestamp >= '29-Mar-2002 13:50' 
and timestamp <= '29-Mar-2002 14:30' and tagname  = SAWTOOTH and numberofsamples = 8
The following query retrieves the MaximumTime value:
select timestamp, value, quality from ihrawdata where 
samplingmode=calculated and calculationmode=MaximumTime and timestamp >= '29-Mar-2002 13:50' 
and timestamp <= '29-Mar-2002 14:30' and tagname  = SAWTOOTH and numberofsamples = 8
The following query retrieves the Minimum value:
select timestamp, value, quality from ihrawdata where 
samplingmode=calculated and calculationmode=Minimum and timestamp >= '29-Mar-2002 13:50' 
and timestamp <= '29-Mar-2002 14:30' and tagname  = SAWTOOTH and numberofsamples = 8
The following query retrieves the MinimumTime value: 
select timestamp, value, quality from ihrawdata where 
samplingmode=calculated and calculationmode=MinimumTime and timestamp >= '29-Mar-2002 13:50' 
and timestamp <= '29-Mar-2002 14:30' and tagname  = SAWTOOTH and numberofsamples = 8
Interval Time Stamp Maximum Maximum Time Minimum Minimum Time Quality
13:55:00.000 0.00 31-Dec-1969 19:00:00.000 0.00 31-Dec-1969 19:00:00.000 0.00
14:00:00.000 22.70 13:59:00.000 17.60 14:00:00.000 100.00
14:05:00.000 47.00 14:02:00.000 2.40 14:03:00.000 100.00
14:10:00.000 12.50 14:08:00.000 10.25 14:03:00.000 100.00
14:15:00.000 10.67 14:10:00.000 6.73 14:15:00.000 100.00
14:20:00.000 6.73 14:15:00.000 5.35 14:20:00.000 100.00
14:25:00.000 4.80 14:20:00.000 4.80 14:25:00.000 100.00
14:30:00.000 4.80 14:30:00.000 4.80 14:30:00.000 100.00

Querying with a single interval so that all samples are included results in the following:

Interval Time Stamp Maximum Maximum Time Minimum Minimum Time Quality
14:30:00.000 47.00 14:02:00.000 2.40 14:03:00.000 100.00

Finding the Minimum and Maximum with Bad Quality Data and Repeated Values

Import the following data:

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
MINMAXBAD,SingleFloat,60,0
 
[Data]
Tagname,TimeStamp,Value,DataQuality
MINMAXBAD,29-Mar-2002 13:59:00.000,22.7,Good
MINMAXBAD,29-Mar-2002 14:01:00.000,12.5,Good
MINMAXBAD,29-Mar-2002 14:02:00.000,47.0,Bad
MINMAXBAD,29-Mar-2002 14:03:00.000,2.4,Bad
MINMAXBAD,29-Mar-2002 14:04:00.000,9.5,Good
MINMAXBAD,29-Mar-2002 14:08:00.000,12.5,Good
MINMAXBAD,29-Mar-2002 14:14:00.000,7.0,Good
MINMAXBAD,29-Mar-2002 14:22:00.000,4.8,Good
Querying with a single interval so that all samples are included results in the following:
Interval Time Stamp Maximum Maximum Time Minimum Minimum Time Quality
14:30:00.000 22.70 13:59:00.00 4.80 14:30:00.000 100.00
Note: MinimumTime is not 14:22 but 14:30. When multiple values within an interval have the same Minimum or Maximum value, (such as here, where two samples have a minimum of 4.8), the newest time stamp is always taken.

Finding the amount of time the collector was running

The following example uses multiple intervals without a filter condition. If the data is good for the entire interval, the returned Value would be the length of the interval in milliseconds.

Import the following data (identical to that used in the examples for Interpolated Data)

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
BADDQTAG,SingleFloat,60,0
 
[Data]
Tagname,TimeStamp,Value,DataQuality
BADDQTAG,29-Mar-2002 13:59:00.000,22.7,Good
BADDQTAG,29-Mar-2002 14:08:00.000,12.5,Bad
BADDQTAG,29-Mar-2002 14:14:00.000,7.0,Bad
BADDQTAG,29-Mar-2002 14:22:00.000,4.8,Good
The following SQL query retrieves data with a start time of 14:00 and an end time of 14:30 in 5-minute intervals: 
select timestamp,value,intervalmilliseconds from ihRawData where 
tagname = baddqtag and samplingmode=calculated and calculationmode=timegood 
and timestamp > '29-mar-2002 13:55:00' and timestamp <'29-mar-2002 14:30:00' 
and intervalmilliseconds=5m
Time Stamp Value Intervalmilliseconds
29-Mar-2002 14:00:00.000 60,000.00 300,000
29-Mar-2002 14:05:00.000 300,000.00 300,000
29-Mar-2002 14:10:00.000 180,000.00 300,000
29-Mar-2002 14:15:00.000 0.00 300,000
29-Mar-2002 14:20:00.000 0.00 300,000
29-Mar-2002 14:25:00.000 180,000.00 300,000
The percent quality for each returned interval is 100.00 and is not shown. By including the intervalmilliseconds column, you can compare the returned milliseconds to the total milliseconds for the interval.
  • When data is good for the whole interval: From 14:01 to 14:05 the data is good, though no raw samples are contained. The value is equal to intervalmilliseconds (300,000).
  • When data starts bad while entering the interval and then turns good: The data is bad going into the 14:21 to 14:25 interval, resulting in a value of 180,000 (out of 300,000).
  • When data is bad from the middle of the interval to the end the interval: The data in the 14:06 to 14:10 interval starts with good quality and changes to bad quality. The value is therefore less than the calculated intervalmilliseconds (180,000 out of 300,000).
  • When there are no raw samples in an interval: The number of raw samples has no effect on the Value; it only affects the percent quality

    The interval from 14:01 to 14:05 contains no raw samples. The data quality throughout the entire interval is good. Therefore, for this interval, the Value is 300,000 (the length of the entire interval).

    The interval from 14:16 to 14:20 contains no raw samples. The data quality throughout the entire interval is bad. At no time in this interval is there good data, so for this interval, the Value is 0.

The following example demonstrates the case of bad data throughout a section in the middle of an interval. The following query retrieves data from a larger interval that has a section of bad quality in the middle of 2 periods of good quality. 
select timestamp,value,intervalmilliseconds from ihRawData where 
tagname = baddqtag and samplingmode=calculated and calculationmode=timegood and 
timestamp >= '29-mar-2002 14:05:00' and timestamp <= '29-mar-2002 14:25:00' and 
intervalmilliseconds=20m
Time Stamp Value Intervalmilliseconds
29-Mar-2002 14:25:00.000 360,000.00

1,200,000

A value of 360,000 milliseconds corresponds to 3 minutes of good quality at the beginning of the interval and 3 minutes of good quality at the end of the interval.