ihTrend Table

The ihTrend table allows you to compare multiple tags for the same timestamp. It contains a row of data for each unique timestamp, but with columns from one or more tags. The column names are dynamic and determined by the returned tag names. The ihTrend table is similar to a pivot table or, for instance, a cross-tab report that you can create in Crystal Reports.

The ihTrend table can store up to 100 columns in a returned set. This allows you to compare Value columns with up to 99 tags for a single timestamp, or Value and Quality columns with up to 49 tags.

Note: Currently, you cannot analyze the ihTrend table in Crystal Reports or the Microsoft SQL Server DTS application.

The following table describes the columns of the ihTrend table, including all possible tag columns. Different queries on this table can produce different column results.

Note: In all column names in the following table, TagID is used as a placeholder for the actual tag name.
Table 1. IhTrend Table
Column NameData TypeDescription
TimeStampVT_DBTimeStampThe date and time that the trend was generated.
TimeStampSecondsVT_DBTimeStampThe date and time for the data sample.
MicrosecondsVT_I4The microsecond interval for the data sample.
SamplingModeVT_BSTRThe mode of sampling data from the archive:
  • CurrentValue: Retrieves the current value. Time frame criteria are ignored.
  • Interpolated: Retrieves evenly spaced interpolated values based on interval or NumberOfSamples and time frame criteria.
  • RawByTime: Retrieves raw archive values based on time frame criteria.
  • RawByNumber: Retrieves raw archive values based on the StartTime, NumberOfSamples, and Direction criteria.
    Note: EndTime criteria are ignored for this mode.
  • RawByFilterToggle: Returns filtered time ranges. The values returned are 0 and 1. If the value is 1, then the condition is true and 0 means false. This mode is used with the time range and FilterTag conditions. Results start and end with timestamps.
  • Calculated: Retrieves evenly spaced calculated values based on NumberOfSamples, interval, time frame, and CalculationMode criteria.
  • Lab: Returns actual collected values without interpolation.
  • Trend: Returns the raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian ignores any leftover values at the end instead of putting them into a smaller interval.
  • Trend2: Returns the raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian puts leftover values into a remainder interval. This mode is more suitable than the Trend mode for analysis of minimums and maximums and for plotting programs that can handle unevenly spaced data.
  • InterpolatedtoRaw: Provides raw data in place of interpolated data when the number of samples is less than the number of available samples.
  • TrendtoRaw: This mode almost always produces the same results as the Trend mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend when the number of actual data samples is less than the requested number of samples.
  • TrendtoRaw2: This mode almost always produces the same results as the Trend2 mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend2 when the number of actual data samples is less than the requested number of samples.
  • LabtoRaw: Provides raw data for the selected calculated data when the number of requested samples is less than the number of available samples.
DirectionVT_BSTRThe direction (forward or backward from the start time) of data sampling from the archive.
NumberOfSamplesVT_I4Number of samples to retrieve from the archive.

Samples will be evenly spaced within the start and end times defined for most sampling modes. For the RawByNumber mode, this column determines the maximum number of values to retrieve. For the RawByTime mode, this column is ignored.

Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
IntervalMillisecondsVT_I4For non-raw sampled data, this column represents a positive integer for the time interval (in milliseconds) between returned samples.
Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
CalculationModeVT_BSTRThis column applies only if the SamplingMode is set to Calculated. It represents the type of calculation to perform on archive data:
  • Average
  • Count
  • Maximum
  • MaximumTime
  • Minimum
  • MinimumTime
  • StandardDeviation
  • Total
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
FilterTagVT_BSTRTagname used to define the filter, if specified. Only a single tag can be specified. Wildcards are not supported.
FilterModeVT_BSTRThe type of time filter:
  • ExactTime: Retrieves data for the exact times that the filter condition is True.
  • BeforeTime: Retrieves data from the time of the last False filter condition to the time of the next True condition.
  • AfterTime: Retrieves data from the time of the last True filter condition to the time of the next False condition.
  • BeforeAndAfterTime: Retrieves data from the time of the last False filter condition to the time of the next False condition.

This value defines how time periods before and after transitions in the filter condition should be handled.

For example, AfterTime indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and ending at the timestamp of the archive value that triggered the False condition.
FilterComparisonModeVT_BSTRThe type of comparison to be made on the filter comparison value:
  • Equal: Filter condition is True when the FilterTag value is equal to the comparison value.
  • EqualFirst: Filter condition is True when the FilterTag value is equal to the first comparison value.
  • EqualLast: Filter condition is True when the FilterTag value is equal to the last comparison value.
  • NotEqual: Filter condition is True when the FilterTag value is not equal to the comparison value.
  • LessThan: Filter condition is True when the FilterTag value is less than the comparison value.
  • GreaterThan: Filter condition is True when the FilterTag value is greater than the comparison value.
  • LessThanEqual: Filter condition is True when the FilterTag value is less than or equal to the comparison value.
  • GreaterThanEqual: Filter condition is True when the FilterTag value is greater than or equal to the comparison value.
  • AllBitsSet: Filter condition is True when the binary FilterTag value is equal to all the bits in the condition. It is represented as ^ to be used in FilterExpression.
  • AnyBitSet: Filter condition is True when the binary FilterTag value is equal to any of the bits in the condition. It is represented as ~ to be used in FilterExpression.
  • AnyBitNotSet: Filter condition is True when the binary FilterTag value is not equal to any one of the bits in the condition. It is represented as !~ to be used in FilterExpression.
  • AllBitsNotSet: Filter condition is True when the binary FilterTag value is not equal to all the bits in the condition. It is represented as !^ to be used in FilterExpression.
This column defines how archive values for the FilterTag value should be compared to the FilterValue value to establish the state of the filter condition. If FilterTag and FilterComparisonValue values are specified, time periods are filtered from the results where the filter condition is False.
FilterValueVT_BSTRThe value with which to compare the FilterTag value to determine appropriate filter times.
FilterExpressionVT_BSTRAn expression which includes one or more filter conditions. The type of conditions used are:
  • AND
  • OR
  • Combination of AND and OR
This column can be used instead of the FilterTag, FilterComparisonMode, and FilterValue columns. While using FilterExpression, the expression is passed within single quotes. For complex expressions, you write the conditions within parentheses. There is no maximum length for FilterExpression, but if called using OLE DB or Excel, these tools may have their own limitations.
TimeZoneVT_BSTRThe type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTimeVT_BOOLIndicates whether Daylight Saving Time logic should be applied to timestamps.
RowCountVT_I4Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.
TagID.ValueVT_VARIANTThe value of the data for the specified tag ID.
TagID.QualityVT_VARIANTFor non-raw sampled data, this column displays the percentage of good quality samples in the interval. For instance, a value of 100 means all samples in the interval are good.

For raw sampled data, data values are:

  • Good
  • Bad
  • Uncertain
  • Not Available
  • Really Unknown
This column also includes the subquality of the data value, if it exists:
  • NonSpecific
  • ConfigError
  • NotConnected
  • DeviceFail
  • SensorFail
  • LastKnownValue
  • CommFailure
  • OutOfService
  • ScaledOutOfRange
  • OffLine
  • NoValue
  • Really Unknown
TagID.TagnameVT_BSTRTagname property of the specified tag ID.
TagID.DescriptionVT_BSTRUser description for the specified tag ID.
TagID.EngUnitsVT_BSTREngineering unit description for the specified tag ID.
TagID.CommentVT_BSTRUser comment associated with the specified tag ID.
TagID.DataTypeVT_BSTRThe data type for the specified tag ID:
  • Scaled
  • SingleFloat
  • DoubleFloat
  • SingleInteger
  • DoubleInteger
  • QuadInteger
  • UnsignedSingleInteger
  • UnsignedDoubleInteger
  • UnsignedQuadInteger
  • FixedString
  • VariableString
  • Byte
  • Boolean
  • BLOB
  • Time
  • Undefined
The data type returned in this column is the data type that you defined in the Historian Administrator application.
TagID.FixedStringLengthVT_UI1This value is 0 unless the data type is FixedString. If the data type is FixedString, this number represents the maximum length of the string value.
TagID.CollectorNameVT_BSTRThe name of the collector responsible for collecting data for the specified tag ID.
TagID.SourceAddressVT_BSTRThe address used to identify the specified tag ID at the data source. For iFIX systems, this is the NTF (Node.Tag.Field).
TagID.CollectionTypeVT_BSTRType of collection used to acquire data for the tag:
  • Unsolicited: The collector accepts data from the source whenever the source presents the data.
  • Polled: The collector acquires data from a source on a periodic schedule determined by the collector.
Note: Not all collectors support unsolicited collection.
TagID.CollectionIntervalVT_I4The time interval, in milliseconds, between readings of data from this tag.

For polled collection, this field represents the time between samples. For unsolicited collection, this field represents the minimum time allowed between samples.

TagID.CollectionOffsetVT_I4The time shift from midnight, in milliseconds, for collection of data from this tag.
TagID.LoadBalancingVT_BOOLIndicates whether the data collector should automatically shift the phase of sampling to distribute the activity of the processor evenly over the polling cycle for the specified tag ID. This is sometimes called phase shifting.
TagID.TimeStampTypeVT_BSTRThe timestamp type applied to data samples at collection time:
  • Source: The source delivers the timestamp along with the data sample.
  • Collector: The collector delivers the timestamp along with the collected data.
TagID.HiEngineeringUnitsVT_R8The high end of the engineering units range. Used only for scaled data types and input scaled tags.
TagID.LoEngineeringUnitsVT_R8The low end of the engineering units range. Used only for scaled data types and input scaled tags.
TagID.InputScalingVT_BOOLIndicates whether the measurement should be converted to an engineering units value. When set to False, the measurement is interpreted as a raw measurement.

When set to True, the system converts the value to engineering units by scaling the value between the HiScale and LoScale values. If not enabled, the system assumes the measurement is already converted into engineering units.

TagID.HiScaleVT_R8The high-end value of the input scaling range used for the tag.
TagID.LoScaleVT_R8The low-end value of the input scaling range used for the tag.
TagID.CollectorCompressionVT_BOOLIndicates whether collector compression is enabled for the specified tag ID.

Collector compression applies a smoothing filter to incoming data by ignoring incremental changes in values that fall within a deadband centered around the last collected value. The collector passes (to the archiver) any new value that falls outside the deadband and then centers the deadband around the new value.

TagID.CollectorDeadbandPercentRangeVT_R4The current value of the compression deadband.
TagID.ArchiveCompressionVT_BOOLIndicates whether archive collector compression is enabled for the tag.
TagID.ArchiveDeadbandPercentRangeVT_R4The current value of the archive compression deadband.
TagID.CollectorGeneral1VT_BSTRThe general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral2VT_BSTRThe general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral3VT_BSTRThe general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral4VT_BSTRThe general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral5VT_BSTRThe general (or spare) configuration fields for the specified tag ID.
TagID.ReadSecurityGroupVT_BSTRThe name of the Windows security group that controls the reading of data for the specified tag ID.

Refer to "Implementing Historian Security" in the Getting Started with Historian manual for definitions of the various security levels and groups.

TagID.WriteSecurityGroupVT_BSTRThe name of the Windows security group that controls the writing of data for the specified tag ID.

Refer to "Implementing Historian Security" in the Getting Started with Historian manual for definitions of the various security levels and groups.

TagID.AdministratorSecurityGroupVT_BSTRThe name of the Windows security group responsible for controlling configuration changes for the specified tag ID.
TagID.CalculationVT_BSTRThe equation for the calculation performed for the specified tag ID.
TagID.LastModifiedVT_DBTimeStampThe date and time that the tag configuration was last modified. The time structure includes milliseconds.
TagID.LastModifiedUserVT_BSTRThe username of the Windows user who last modified the tag configuration.
TagID.CollectorTypeVT_BSTRThe type of collector responsible for collecting data for the specified tag ID:
  • Undefined
  • iFIX
  • Simulation
  • OPC
  • File
  • iFIXLabData
  • ManualEntry
  • Simulation
  • Other
TagID.StoreMillisecondsVT_BOOLIndicates whether time resolution in milliseconds is enabled for the specified tag ID.

If not enabled, time resolution is in seconds instead of milliseconds. Maximum data compression is achieved when this value is set to False. This is the optimum setting for most applications.

TagID.UTCBiasVT_I4The time zone bias for the specified tag ID. Time zone bias is used to indicate the natural time zone of the tag expressed as an offset from UTC (Universal Time Coordinated) in minutes.

UTC is the international time standard, the current term for what was commonly referred to as Greenwich Mean Time (GMT).

TagID.AverageCollectionTimeVT_I4The average time it takes to execute the calculation tag since you started the Calculation Collector for the specified tag ID.
TagID.CollectionDisabledVT_I4Indicates whether collection is enabled (0) or disabled (1) for the specified tag ID. The default setting is enabled (0).
TagID.CollectorCompressionTimeoutVT_I4Indicates the maximum amount of time the collector will wait between sending samples to the archiver. This time is kept per tag, as different tags report to the archiver at different times.

This value should be in increments of your collection interval, and not less.

Ideally, this field is used for polled data values. It can be used with unsolicited data, but when you do so, you are dependent on the data source for the value to change. With unsolicited data, since Historian only records the value when it changes, the actual time before the timeout might exceed the compression timeout.

TagID.ArchiveCompressionTimeoutVT_I4Indicates the maximum amount of time from the last stored point before another point is stored, if the value does not exceed the archive compression deadband for the specified tag ID.
TagID.InterfaceAbsoluteDeadbandingVT_BOOLIndicates whether absolute collector deadbanding is enabled for the specified tag ID.
TagID.InterfaceAbsoluteDeadbandVT_R8Indicates the value for absolute collector deadbanding.
TagID.ArchiveAbsoluteDeadbandingVT_BOOLIndicates whether absolute archive deadbanding is enabled for the specified tag ID.
TagID.ArchiveAbsoluteDeadbandVT_R8Indicates the value for absolute archive deadbanding.
TagID.SpikeLogicVT_BOOLIndicates whether Spike Logic is enabled on the collector.
TagID.SpikeLogicOverrideVT_BOOLIndicates whether the Spike Logic setting for the specified tag ID overrides the collector setting (True) or the collector setting is used (False).

Use care when building queries against the ihTrend table. Because a query to this table compares multiple tags at the same time, it takes longer to query the ihTrend table than it does the ihRawData table. The ihTrend table can be quite large, so be sure to either use the default start and end times, or define a specific time interval. See Query Performance Optimization for more ideas on how to optimize your query of the ihTrend table.

ihTrend Examples

Example SQL statements for the ihTrend table are outlined in the following examples.

Example 1: Retrieve Value and Quality of the First 50 Tags

SELECT timestamp, *.value, *.quality FROM ihtrend

Example 2: Retrieve Value of the First 100 Tags

SELECT timestamp, *.value FROM ihTrend

Example 3: Retrieve Values of All Tags That Match a Specific Pattern

SELECT timestamp,*0001.value FROM ihtrend ORDER BY MY_SERVER.Simulation00001.Value

Example 4: Retrieve Hourly Interpolated Values of TagNames That Match *0001

SET samplingmode=interp, intervalmilliseconds=1h
SELECT timestamp, *0001.value FROM ihtrend
ORDER BY Simulation00001.value DESC, timestamp DESC

Example 5: Retrieve Maximum Values of All TagNames That Match *0001

The following example shows how to use a TagName (simulation.00001.Value) in a WHERE clause.

SELECT timestamp, *0001.value FROM ihtrend
WHERE timestamp>='28-nov-2001 00:00'
AND timestamp<='29-nov-2001 00:00:00'
AND samplingmode=calc
AND intervalmilliseconds=1h
AND calculationmode=max
AND simulation00001.Value > 1000 ORDER BY timestamp

Example 6: Select Interpolated Values for All Single Float Tags

The following example shows how to select interpolated values for all single float tags, without doing a JOIN with the ihTags table to retrieve the DataType property.

SELECT timestamp, *.value,*.description FROM ihtrend
WHERE timestamp>>='28-nov-2001 00:00'
AND timestamp<='29-nov-2001 00:00:00'
AND samplingmode=calculated
AND intervalmilliseconds=2h
AND *.datatype = singlefloat ORDER BY timestamp

Example 7: Select Interpolated Data for TagNames That Match sim*

The following example shows how to sort the returned rows by a TagName , simulation.00001.Value.

SET starttime='28-nov-2001 00:00', endtime='29-nov-2001 00:00:00', samplingmode=interp, intervalmilliseconds=1h
SELECT timestamp, sim*.*, sim*.description, sim*.lastmodifieduser FROM ihtrend
WHERE sim*.description LIKE '*sim*'
AND sim*.description like '*first*'
AND *.datatype = singlefloat
ORDER BY simulation00001.value DESC, timestamp