Returns data from one or more variables, comma-separated, informed in the @node parameter, according to the format defined by the aggregation function informed in the @aggregateFunction parameter.

Syntax

SELECT Name, Timestamp, Quality, <type_function>(Value)
  FROM EpmQueryAggregateFunction(<[float]@timeZoneOffset>,
  <[datetime]@startTime>, <[datetime]@endTime>, <[bigint]@sampleInterval>,
  <[string]@aggregateFunction>, <[string]@node>)

Parameters

•<type_function>: Conversion function to define a data type for the resulting values on the Value column. Possible values are described on topic EpmReadPropertyFunction

•<[float]@timeZoneOffset: Timezone to consider. Possible values range from -12 to 14

•<[datetime]@startTime>: Initial date of the period to retrieve. This must be a String in the format yyyy-MM-dd HH:mm:ss

•<[datetime]@endTime>: Final date of the period to retrieve. This must be a String in the format yyyy-MM-dd HH:mm:ss

•<[bigint]@sampleInterval>: Time interval to use when calculating the aggregation, in milliseconds

•<[string]@aggregateFunction>: Name of an aggregation function to use. Possible values for this parameter are:

•Trend: Interpolates data for a better visualization on charts

•Interpolative: Interpolates data in the informed interval, returning points as defined in the @sampleInterval parameter

•Start: Equivalent to the Interpolative function, but uses the original timestamp of the first value of the sampling interval

•End: Equivalent to the Interpolative function, but uses the original timestamp of the last value of the sampling interval

•StartBounds: Equivalent to the Interpolative function, but uses the original timestamp of the first value of the sampling interval. It considers the Simple Bounding Values

•EndBounds: Equivalent to the Interpolative function, but uses the original timestamp of the last value of the sampling interval. It considers the Simple Bounding Values

•Average: Calculates the average of data values with good quality in the sampling interval defined in the @sampleInterval parameter

•TimeAverage: Calculates the average weighted by time using the Interpolated Bounding Values method in the sampling interval defined in the @sampleInterval parameter

•TimeAverage2: Calculates the average weighted by time using the Simple Bounding Values method in the sampling interval defined in the @sampleInterval parameter

•Total: Returns the sum of data from the sampling interval defined in the @sampleInterval parameter

•Total2: Returns the sum of data with good quality from the sampling interval defined in the @sampleInterval parameter

•Count: Returns the number of data available in the sampling interval defined in the @sampleInterval parameter

•Minimum: Informs the minimum value found on data in the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the initial time of each sampling interval

•Maximum: Informs the maximum value found on data in the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the initial time of each sampling interval

•MinimumActualTime: Informs the minimum value found on data in the sampling interval defined in the @sampleInterval parameter, except that the timestamp displayed corresponds to the moment of the occurrence of the minimum value found

•MaximumActualTime: Informs the maximum value found on data in the sampling interval defined in the @sampleInterval parameter, except that the timestamp displayed corresponds to the moment of the occurrence of the maximum value found

•Range: Returns the difference between the minimum and maximum value found in the sampling interval defined in the @sampleInterval parameter. It ignores values with a bad quality

•Minimum2: Informs the minimum value found on data in the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the initial time of each sampling interval. It considers the Simple Bounding Values

•Maximum2: Informs the maximum value found on data in the sampling interval defined in the @sampleInterval parameter. The timestamp corresponds to the initial time of each sampling interval. It considers the Simple Bounding Values

•MinimumActualTime2: Informs the minimum value found on data in the sampling interval defined in the @sampleInterval parameter, except that the timestamp displayed corresponds to the moment of the occurrence of the minimum value found. It considers the Simple Bounding Values

•MaximumActualTime2: Informs the maximum value found on data in the sampling interval defined in the @sampleInterval parameter, except that the timestamp displayed corresponds to the moment of the occurrence of the maximum value found. It considers the Simple Bounding Values

•Range2: Returns the difference between the minimum and maximum value found in the sampling interval informed in the @sampleInterval parameter. Ignores values with bad quality and considers the Simple Bounding Values

•Delta: Returns the difference between the first and last values with good quality in each sampling interval

•DeltaBounds: Returns the difference between the first and last values with good quality in the sampling interval. It considers the Simple Bounding Values

•AnnotationCount: Informs the number of annotations a variable has in the sampling interval

•DurationInStateZero: Returns the total time, in milliseconds, in which a value was equal to 0 (zero) in the sampling interval

•DurationInStateNonZero: Returns the total time, in milliseconds, in which a value was different from 0 (zero) in the sampling interval

•PercentInStateZero: Returns the time percentage, relative to the total time of the sampling interval, in which a value was equal to 0 (zero)

•PercentInStateNonZero: Returns the time percentage, relative to the total time of the sampling interval, in which a value was different from 0 (zero)

•NumberOfTransitions: Informs how many transitions occurred in the sampling interval between values  0 (zero) and 1 (one)

•DurationGood: Returns the time, in milliseconds, in which data was with good quality in the sampling interval. Quality is based on the quality of data from interval's boundaries

•DurationBad: Returns the time, in milliseconds, in which data was with bad quality in the sampling interval. Quality is based on the quality of data from interval's boundaries

•PercentGood: Returns a time percentage, relative to the time of the sampling interval, in which data had good quality

•PercentBad: Returns a time percentage, relative to the time of the sampling interval, in which data had bad quality

•WorstQuality: Returns the worst quality in the interval specified in the @sampleInterval parameter

•WorstQuality2: Returns the worst quality found in the interval specified in the @sampleInterval parameter. It considers the Simple Bounding Values

•StandardDeviationPopulation: Returns the standard population deviation (n) in the requested interval. It considers the Simple Bounding Values

•VariancePopulation: Returns the variance for the StandardDeviationPopulation function

•StandardDeviationSample: Returns the standard sampling deviation (n - 1) in the requested interval

•VarianceSample: Returns the variance for the StandardDeviationSample function

•<[string]@node>: Variable or variables to retrieve. Users must inform a full name or names between single quotes and separated by commas

Result Fields

•Name: Name of a variable or variables retrieved

•Timestamp: Timestamp of data

•Quality: Quality of data

•Value: Value of data

Example

To return data from the RandomTags_Random1 variable in the period between 10:23 and 11:23 of 12/07/2020, with a timezone of -3 (minus three) hours and interpolated at every 5 (five) minutes, use the next statement.

SELECT Name, Timestamp, Quality, doubleval(Value) FROM
  EpmQueryAggregateFunction(-3, '2020-12-07 10:23:00', '2020-12-07 11:23:00',
  300000, 'Interpolative', 'RandomTags1')

EpmQueryAggregateFunction function