Description
Issues a query to to the SQLTags Historian. Querying tag history involves specifying the tags and the date range, as well as a few optional parameters. The SQLTags historian will find the relevant history and then interpolate and aggregate it together into a coherent, tabular result set.
This function takes a list of strings, where each string is a tag path, like "Tanks/Tank5" or "[OracleProvider]Sump/Out2". See also: Tag Paths.
The return size determines how the underlying data is aggregated and/or interpolated. If a distinct return size is specified, that will be the number of rows in the resulting dataset. The special numbers 0 and -1 mean "Natural" and "On-Change", respectively. "Natural" calculates a return size based on the rate of the logging historical scan classes. For example, if you query 1 hour of data for a scan class logging every minute, the natural return size is 60. "On-Change means that you'll get an entry whenever any of the tags under consideration have changed.
Instead of defining a fixed return size, the parameters intervalHours and intervalMinutes can be used. These parameters can be used independently or together to define a "window size". For example, if you defined a 1 hour range, with intervalMinutes=15, you would get 4 rows as a result.
The span of the query can be specified using startDate and endDate. You can also use rangeHours and rangeMinutes in conjunction with either start or end date to specify the range in dynamic terms. For example, you could specify only "rangeHours=-8" to get the last 8 hours from the current time. Or you could use "startDate='2012-05-30 00:00:00', rangeHours=12" to get the first half of the day for May 30th, 2012.
The aggregation mode is used when the data is denser than what you asked for. This happens when using fixed return sizes, as there will often be multiple raw values for the window interval defined. Another common operation is to set the return size to 1, in order to use these aggregate functions for calculation purposes. The available functions are:
| • | "MinMax" - will return two entries per time slice - the min and the max. |
| • | "Average" - will return the time-weighted average value of all samples in that time slice. |
| • | "LastValue" - returns the most recent actual value to the end of the window. |
| • | "SimpleAverage" - returns the simple mathematical average of the values - ((V1+V2+...+Vn)/n) |
| • | "Maximum" - the maximum value of the window. |
| • | "Minimum" - the minimum value of the window. |
This function accepts keyword-style invocation. See also: Functions / Keyword Invocation
Syntax
system.tag.queryTagHistory(paths, startDate, endDate, returnSize, aggregationMode, returnFormat, columnNames, intervalHours, intervalMinutes, rangeHours, rangeMinutes, aggregationModes, includeBoundingValues, validateSCExec, noInterpolation, ignoreBadQuality)
Parameters
PySequence paths - An array of tag paths (strings) to query. Each tag path specified will be a column in the result dataset.
Date startDate - The earliest value to retrieve. If omitted, 8 hours before current time is used.
Date endDate - The latest value to retrieve. If omitted, current time is used.
Integer returnSize - The number of samples to return. -1 will return values as they changed, and 0 will return the "natural" number of values based on the logging rates of the scan class(es) involved. -1 is the default.
String aggregationMode - The mode to use when aggregating multiple samples into one time slice. Valid values are: "Average" (time-weighted), "MinMax", "LastValue", "SimpleAverage", "Sum", "Minimum", and "Maximum".
String returnFormat - Use "Wide" to have a column per tag queried, or "Tall" to have a fixed-column format. Default is "Wide".
PySequence columnNames - Aliases that will be used to override the column names in the result dataset. Must be 1-to-1 with the tag paths. If not specified, the tag paths themselves will be used as column titles.
Integer intervalHours - Allows you to specify the window interval in terms of hours, as opposed to using a specific return size.
Integer intervalMinutes - Same as intervalHours, but in minutes. Can be used on its own, or in conjunction with intervalHours.
Integer rangeHours - Allows you to specify the query range in hours, instead of using start and end date. Can be positive or negative, and can be used in conjunction with startDate or endDate.
Integer rangeMinutes - Same as rangeHours, but in minutes.
PySequence aggregationModes - A one-to-one list with paths specifying an aggregation mode per column.
Boolean includeBoundingValues - A boolean flag indicating that the system should attempt to include values for the query bound times if possible. The default for this property depends on the query mode, so unless a specific behavior is desired, it is best to not include this parameter.
Boolean validateSCExec - A boolean flag indicating whether or not data should be validated against the scan class execution records. If false, data will appear flat (but good quality) for periods of time in which the system wasn't running. If true, the same data would be bad quality during downtime periods.
Boolean noInterpolation - A boolean flag indicating that the system should not attempt to interpolate values in situations where it normally would. This will also prevent the return of rows that are purely interpolated.
Boolean ignoreBadQuality - A boolean flag indicating that bad quality values should not be used in the query process. If set, any value with a "bad" quality will be completely ignored in calculations and in the result set.
Returns
Dataset - A dataset representing the historian values for the specified tag paths. The first column will be the timestamp, and each column after that represents a tag.
Scope
All