The wwVersion time domain extension allows you to specify which version of the stored data is to be retrieved. The storage subsystem handles versioning by creating new values to represent updates and inserts. The values containing the updates/inserts have a creation date, which is used to version the data.
If a number of updates or inserts have been made, there will be a number of timestamped versions. However, there will be two possible versioning types that you can use to access data: ORIGINAL and LATEST.
The REALTIME value for wwVersion is not applicable for retrieval.
Note The values ORIGINAL and LATEST are not case-sensitive.
If you do not specify the version, the latest modified data value will be retrieved.
For example:
SELECT TagName, DateTime, Value, wwVersion FROM History
WHERE TagName IN ('SysTimeHour', 'SysTimeMin') AND DateTime >= '2001-12-20 0:00'
AND DateTime <= '2001-12-20 0:05' AND wwRetrievalMode = 'Delta' AND wwVersion = 'Original'
The results are:
When retrieving the latest version, the wwVersion parameter always returns with a value of LATEST for all values, even though many of the values may actually be the original values that came from the I/O Server. To distinguish between an actual latest value and an original value, a special QualityDetail of 202 is returned for a good, latest point.
For example:
SELECT DateTime, Value, Quality, QualityDetail, OPCQuality, wwVersion FROM History
WHERE TagName IN ('PV')
AND DateTime >= '2005-04-17 11:35:00' AND DateTime <= '2005-04-17 11:36:00' AND wwRetrievalMode = 'Delta'
AND wwVersion = 'Latest'
TagName DateTime Value wwVersion
SysTimeMin 2001-12-20 00:00:00.000 0.0 ORIGINAL SysTimeHour 2001-12-20 00:00:00.000 0.0 ORIGINAL SysTimeMin 2001-12-20 00:01:00.000 1.0 ORIGINAL SysTimeMin 2001-12-20 00:02:00.000 2.0 ORIGINAL SysTimeMin 2001-12-20 00:03:00.000 3.0 ORIGINAL SysTimeMin 2001-12-20 00:04:00.000 4.0 ORIGINAL SysTimeMin 2001-12-20 00:05:00.000 5.0 ORIGINAL
The results are:
wwInterpolationType
Where applicable, the interpolation type controls how retrieval will determine which analog value to return at a given cycle boundary. Valid values are STAIRSTEP and LINEAR. If STAIRSTEP is specified, no interpolation occurs. The last known point is returned with the given cycle time. If no valid value can be found, a NULL is returned. If LINEAR is specified, the system calculates a new value at the given cycle time by interpolating between the last known value prior to the cycle time and the first value after the cycle time. The type of data that you want to retrieve usually determines the interpolation type to use. For example, if you have a thermocouple, the temperature change is linear, so it would be best to use linear interpolation. If you have a tag that contains discrete measurements, such as a set point, then you would probably want to use stair-stepped values. In general, it is recommended that you use linear interpolation as the general setting, and use stair-stepped values for the exceptions.
The setting is ignored for non-analog tags and for cyclic queries, because cyclic queries always return stair-stepped values.
Linear interpolation can be set on three levels within the system:
•
The IndustrialSQL Server historian system-wide setting. The system-wide setting must be either stair-step or interpolated. For more information, see "System Parameters" on page 26.•
The individual analog tag setting. Using the InSQL Configuration Editor, you can configure an individual analog tag to use the system-wide setting or either stair-stepped values or linear interpolation. The individual tag setting will override the system-wide setting.•
The setting for the wwInterpolationType parameter in the query. This setting overrides any other setting for all tags in the query.If all of the following conditions occur, then the retrieval subsystem simply extends this value and returns the value V1 at time Tc.
•
The interpolation type is set to STAIRSTEP.DateTime Value Quality QualityDetail OPCQuality wwVersion
2005-04-17 11:35:00.000 12.5 0 192 192 LATEST 2005-04-17 11:35:15.000 17.3 0 192 192 LATEST 2005-04-17 11:35:30.000 34.0 0 202 192 LATEST 2005-04-17 11:35:45.000 43.1 0 192 192 LATEST 2005-04-17 11:36:00.000 51.2 0 192 192 LATEST
•
The most recent point stored for the tag is P1.Similarly, for linear interpolation, if either of the points represents a NULL, retrieval will return P1 at time Tc. If both points, however, are non-NULL,
retrieval calculates the value Vc as the value where a straight line through both
points intersects with the cycle boundary. Expressed as a formula, Vc is calculated as:
Vc = V1 + ((V2 - V1) * ((Tc - T1) / (T2 - T1)))
The wwInterpolationType parameter is dynamically used both for input for the query, when you need to override the individual tag settings, and for output for each individual row to show whether a particular row value was calculated using linear interpolation (returned as "LINEAR") or if it is a stair-stepped value (returned as "STAIRSTEP").
To force a query to always use linear interpolation whenever applicable, specify the following in the query:
AND wwInterpolationType = 'Linear'
To force a query to always return stair-stepped values, specify the following in the query:
AND wwInterpolationType = 'StairStep'
wwTimeStampRule
When applicable, use the timestamp rule to specify whether cyclic results are timestamped at the beginning of the cycle or at the end of the cycle. Valid values are START and END. If no timestamp rule is specified in the query, then retrieval uses the setting of the TimeStampRule system parameter. To force a query to timestamp results at the start of a cycle, specify the following in the query:
AND wwTimeStampRule = 'Start'
To force a query to timestamp results at the end of a cycle, specify the following in the query:
AND wwTimeStampRule = 'End'
If you include the wwTimeStampRule column in your SELECT statement, it will show which timestamp rule has been applied for the individual row, if applicable.
wwQualityRule
Where applicable, the quality rule can be used to specify whether values with certain characteristics are explicitly excluded from consideration by data retrieval. This parameter will override the setting of the QualityRule system parameter. Valid values are GOOD or EXTENDED. A quality rule of GOOD means that data values with doubtful OPC quality will not be used in the retrieval calculations. A quality rule of EXTENDED means that data values with both good and doubtful OPC quality will be used in the retrieval calculations.
To force a query to exclude points with doubtful OPC quality, specify the following in the query:
AND wwQualityRule = 'Good'
To force a query to use points with both good and doubtful OPC quality, specify the following in the query:
AND wwQualityRule = 'Extended'
wwEdgeDetection
An event is the moment at which a detection criterion is met on historical tag values in the IndustrialSQL Server historian. At a basic level, anything that can be determined by looking at stored data can be used as an event.
When detecting events, it is useful to pinpoint rows in a result set where the satisfaction of criteria in a WHERE clause changed from true to false, or vice- versa. For example, you may want to know when the level of a tank went above 5 feet. The WHERE clause in a query for this example might be TANKLEVEL > 5. As the tank level approaches 5 feet, the criterion does not return true. Only when the level crosses the line from not satisfying the criterion to satisfying it, does the event actually occur. 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:
Edge detection only applies to analog and discrete value detectors. Also, edge detection is handled slightly differently based on whether you are using analog tags or discrete tags.
For more information on the event system, see Chapter 7, "Event Subsystem."
Option Results
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.