Historian Server Time Domain Extensions

The SQL language is not always appropriate for handling time series data. Historian Server adds several extensions to the normal SQL language to make it possible to meaningfully query this type of information. You can retrieve data in the Historian Server extension tables using normal Transact-SQL code, as well as the specialized SQL time domain extensions, also known as retrieval options. 

The extensions are implemented as virtual columns in the extension tables. When you query an extension table, you can specify values for these column parameters to manipulate the data that will be returned. You will need to specify any real-time extension parameters each time that you execute the query.
The time domain extensions are:

1. wwCycleCount
2. wwResolution
3. wwRetrievalMode
4. wwTimeDeadband
5. wwValueDeadband
6. wwEdgeDetection
7. wwTimeZone
8. wwVersion
9. wwInterpolationType
10. wwTimeStampRule
11. wwQualityRule
12. wwValueSelector
13. wwStateCalc
14. wwFilter

wwCycleCount

This is an integer value in milliseconds. It is the sampling interval to retrieve data from any of the history tables of the historian in 'Cyclic' mode. This sets the time interval between samples to be returned over the time period specified by the start and end DateTime of the query. wwCycleCount and wwResolution are mutually exclusive.

wwValueDeadband

This a float value specifying a percentage of a tag's full scale in engineering units. In 'Delta' mode, it is used to control when a change in value is considered to occur. It can have any value between 0 and 100.
wwTimeDeadband applies only to delta retrieval.

wwTimeDeadband

This is an integer value in milliseconds that determines the minimum time between returned values for a single tag. Values will not be returned more frequently than this, even if the underlying data changes many times within the interval.

The deadband base value is reset each time that a value is returned, so that the last value returned acts as the basis for the deadband.

wwTimeDeadband applies only to delta retrieval. 

Edge Detection (wwEdgeDetection)

This extension enables detection of a value change using ‘edge’ conditions. For example, it can be used to determine the point in time that a tag goes into (leading) or comes out of (trailing) alarm state. This imaginary line where the change occurs is called the edge.

Over a period of time, there may be many instances where the criteria cross the edge from being satisfied to not satisfied, and vice-versa. The values on either side of this edge can be detected if you configure your event tag to include this information. There are four possible options for edge detection: none, leading, trailing, or both. You will get differing results based on which option you use:

• None – Returns all rows that successfully meet the criteria; no edge detection is implemented at the specified resolution
• Leading – Returns only rows that are the first to successfully meet the criteria (return true) after a row did not successfully meet the criteria (returned false)
• Trailing – Returns only rows that are the first to fail the criteria (return false) after a row successfully met the criteria (returned true)
• Both – All rows satisfying both the leading and trailing conditions are returned

Analog Value Filtering (wwFilter)

You can use the following analog filters for all retrieval modes:

1. Statistical removal of outliers
2. Analog to discrete conversion
3. Zero around a base value

These filters are applied in a query to retrieve data from the History table, WideHistory table, or StateWideHistory table. These filters apply to analog tags only. All other types of tags, including analog summary tags, are not supported.

1. Statistically Removing Outliers (SigmaLimit)

This analog filter removes outliers from a set of analog points based on the assumption that the distribution of point values in the set is a normal distribution.

You can filter outliers by specifying a filter called 'SigmaLimit()'. This filter has one parameter defined for specifying the value of n. This parameter is of type double. If the parameter is omitted, then a default parameter of 2.0 is used.

2. Converting Analog Values to Discrete Values (ToDiscrete)

The analog to discrete conversion filter allows you to convert value streams for any analog tag in the query tag list into discrete value streams. The filter can be used with all the retrieval modes.
To convert analog values to discrete values, specify the ToDiscrete() filter in the wwFilter column. This filter has two parameters:

2. Zero around a base value

This analog filter lets you force values in a well-defined range around one or more base values to “snap to” that base value. For example, you can use this filter when a tank is known to be empty, but the tag that stores the tank level returns a noisy value close to zero.
The filter can be used with all retrieval modes, but its main benefits are in the aggregate retrieval modes: average, integral, minimum, and maximum. 
To zero values around the base value, specify the SnapTo() filter in the wwFilter column of the query.
The syntax for this filter is:
SnapTo([tolerance[,base_value_1[, base_value_2]…]])
This filter has two parameters.
 

Parameter	Valid Values	Default Value
Tolerance	Any double value	0.01
BaseValue	Zero, one, or up to 100 comma-separated double values	Single base value of 0.0

 
When the SnapTo filter is specified, point values falling inside any of the ranges [Base value - Tolerance, Base value + Tolerance] will be forced to the base value before the point goes into further retrieval processing.
If ranges overlap, the first matching base value will be used.