ihComments Table

The ihComments table contains the annotations associated with the collected data. There is a separate row of data in the ihComments table for each comment associated with a tag. For instance, you can have five rows that contain the same tag and timestamp, but each contain a different comment value.

It is possible to have different data types of annotations. Comments are most often strings, but can be binary numbers or BLOBs. Only string comments are returned in the ihComments table.

The following table describes the columns of the ihComments table.
Table 1. ihComments Table
Column NameData TypeDescription
TagnameVT_BSTRTagname property of the tag.
Note: There is no length limit for Historian tag names in the Data Archiver. However, different client applications may have their own limits.
TimeStampVT_DBTimeStampThe date and time that the data was generated.
TimeStampSecondsVT_DBTimeStampThe date and time that the data was generated.
MicrosecondsVT_I4The microsecond portion of the date and time.
StoredOnTimeStampVT_DBTimeStampThe date and time that the comment was generated.
StoredOnTimeStampVT_DBTimeStampThe time that the comment was added to the archive.
SuppliedUsernameVT_BSTRThe username of the currently logged-in Windows user at the time that the comment was entered.
UsernameVT_BSTRUsername provided along with the comment.
CommentVT_BSTRThe actual comment.
DataTypeHintVT_BSTRName of the data type for the comment:
  • String
  • Read-only
  • Optional
SamplingModeVT_BSTRThe mode used to sample 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 sampling 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 sampling mode is used with the time range and FilterTag conditions. Results have starting and ending 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 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 raw minimum and maximum values 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 creates as many intervals of the interval length as will fit into the sampling period, and then creates a remainder interval from whatever time is left. This sampling 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 requested 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 a greater number of samples are requested 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 sampling mode almost always produces the same results as the Trend2 mode. The exception is that when a greater number of samples are requested 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 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 from the archive to retrieve.

Samples will be evenly spaced within the time range defined by start and end times for most sampling modes. For the RawByNumber mode, this column determines the maximum number of values to retrieve. For the RawByTime mode, this value 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_BSTRThe calculation mode, if used.
FilterTagVT_BSTRTagname used to define the filter, if specified. Only a single tag can be specified, and 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.

The FilterMode 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 FilterTag values should be compared to FilterValue values 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, and 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.

ihComments Examples

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

Example 1: Retrieve All Comments for a Specific Tag for This Month

SELECT * FROM ihcomments WHERE tagname LIKE '*001'
AND timestamp>bom

Example 2: Retrieve Comments That Contain a Substring

SELECT * FROM ihcomments WHERE comment LIKE '*abc*'

Example 3: Retrieve All Comments in an Archive

SELECT * FROM ihComments WHERE timestamp<=Now
AND samplingmode=rawbytime