ihRawData Table

The ihRawData table contains any collected data for each tag contained in the Historian server. It contains not just raw data, but also calculated data and interpolated data. This table is the one typically used for reporting.

There is one row in the ihRawData table for each combination of tagname and timestamp. For instance, you can have two rows for the same tag, each with different timestamps. You can retrieve data for more than one tag name in a simple query.

The following table describes the columns of the ihRawData table.

Table 1. ihRawData 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 for the data sample.
TimeStampSecondsVT_DBTimeStampThe date and time for the data sample.
MicrosecondsVT_DBTimeStampThe microsecond interval for the data sample.
ValueVT_VARIANTThe value of the data.
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
OPCQualityValidVT_BSTRIndicates whether the OPCQuality column contains valid real OPC quality. A value of 0 indicates that you should ignore the OPCQuality field, and a value of 1 indicates that the OPCQuality column contains valid real OPC quality.
OPCQualityVT_I4Indicates the OPC quality as delivered by the OPC server to the Historian OPC collector. The exact meaning of the bits depends on the OPC specification and the OPC server documentation. Typically, a value of 0 represents bad quality, and a value of 192 represents good quality.
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 the 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 filter tag conditions. The result starts with a starting timestamp and ends with an ending timestamp.
  • Calculated: Retrieves evenly spaced calculated values based on NumberOfSamples, interval, the time frame criteria, and the CalculationMode criteria.
  • Lab: Returns actual collected values without interpolation.
  • Trend: Returns raw minimum and raw maximum values for each specified interval. Use this sampling 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, rather than putting them into a smaller interval.
  • Trend2: Returns raw minimum and raw maximum values for each specified interval. Use this sampling mode to maximize performance when retrieving data points for plotting. Also, 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 is less than the available samples.
  • TrendtoRaw: This sampling 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. TrendtoRaw 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 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. TrendtoRaw2 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 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 the 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_BSTR

This 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, and wildcards are not supported.
FilterModeVT_BSTR

The 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 next False condition.
  • BeforeAndAfterTime: Retrieves data from the time of the last False filter condition to the next False condition.

This mode 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 leading up to 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
FilterExpression can be used instead of the FilterTag, FilterComparisonMode and FilterValue parameters. While using FilterExpression, the expression is passed within single quotes. For complex expressions, write the conditions within parentheses. There is no maximum length for the FilterExpression value, but if called using OLE DB or Excel, those 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.

The ihRawData table can generate a large number of rows if not used with caution. You can easily generate queries which take a very long time to complete and put stress on the archiver and generate network traffic.

ihRawData Examples

Tasks that you might want to perform on the ihRawData table are outlined in the following examples.

Example 1: Retrieve All Samples With a Value Outside the Query Supplied Values

SELECT * FROM ihRawData WHERE value<140000 OR value>150000

Example 2: Retrieve All Bad Samples (Raw Data)

SELECT * FROM ihRawData WHERE quality NOT LIKE good*
AND samplingmode=RawbyTime

Example 3: Count Bad Samples (Raw Data)

SELECT COUNT(*) FROM ihRawData WHERE quality NOT LIKE good*
AND samplingmode=RawbyTime

Example 4: Retrieve All Bad Samples Over the Last Day (Interpolated Data)

SELECT timestamp, tagname, value, quality FROM ihRawData
WHERE samplingmode=rawbytime
AND Quality NOT LIKE good*
AND timestamp>=Now-24H

Example 5: Use an Explicit Time Zone

SELECT * FROM ihRawData WHERE timezone=300

Example 6: Perform a Simple Sequence of Events

SELECT timestamp, tagname, value, quality FROM ihrawdata
WHERE samplingmode=rawbytime ORDER BY timestamp

Example 7: Report the Busiest Tags

SELECT tagname, value FROM ihRawData
WHERE samplingmode=calculated
AND calculationmode=count
AND numberofsamples=1
AND timestamp>='07/30/2002 10:00:00'
AND timestamp<='07/30/2002 11:00:00' order by value descending

Example 8: Retrieve All Bad Samples Over the Last Day

SELECT timestamp, tagname, value, quality FROM ihRawData
WHERE samplingmode=rawbytime
AND Quality NOT LIKE good*
AND timestamp>=Now-24H

Example 9: Retrieve All Bad Samples, Ignore End of Collection Markers

SELECT timestamp, tagname, value, quality FROM ihRawData
WHERE samplingmode=rawbytime
AND Quality NOT LIKE good*
AND quality NOT LIKE 'bad offline' AND timestamp>=Now-24H

Example 10: Count Bad Samples, Ignore End of Collection Markers

SELECT COUNT(*) FROM ihRawData WHERE samplingmode=rawbytime
AND Quality NOT LIKE good* and Quality NOT LIKE 'bad offline'
AND timestamp>=Now-24H

Example 11: Obtain All Raw Samples With Comments From Yesterday

SELECT ihRawData.Tagname, ihRawData.TimeStamp, ihRawData.Value
FROM ihRawData
INNER JOIN ihComments ON ihComments.Tagname = ihRawData.Tagname
AND ihComments.Timestamp = ihRawData.Timestamp
AND ihComments.Comment = "The comment" WHERE samplingmode=rawbytime
AND ihComments.Timestamp > Yesterday
AND ihComments.Timestamp < Today

Example 12: Determine the Number of Milliseconds Per Interval With Good Data

SELECT timestamp, tagname, value as TimeGood, quality, intervalmilliseconds FROM ihRawData 
WHERE tagname=Denali.Simulation00001
AND samplingmode=calculated
AND calculationmode=timegood
AND intervalmilliseconds=10s
AND timestamp>='1/20/2003 13:18:00'
AND timestamp<='1/20/2003 13:20:00'

Example 13: Retrieve Raw Minimum and Maximum Values Per Interval

In this example, you use the data retrieved from the query (with the Trend sampling mode) to plot points.

SELECT timestamp, tagname, value, quality
FROM ihRawData
WHERE tagname=dFloatTag5
AND samplingmode=trend
AND intervalmilliseconds=24h
AND timestamp>='1/01/2003 07:00:00'
AND timestamp<='1/10/2003 12:00:00'

Example 14: Retrieve Data with Native Values and Tags Associated With Enumerated Sets

If enumsetrawvalue is set to False, the data is retrieved with string values by default. If enumsetrawvalue is set to True, the raw values are retrieved. Once set, these values are retrieved by default for the current session and will only change when you open a new session.

SELECT * from ihrawdata
WHERE samplingmode='rawbytime' and tagname=mytag
AND enumsetrawvalue=TRUE

SELECT timestamp,value,quality from ihrawdata WHERE tagname = MyTag
AND samplingmode=Interpolated and numberofsamples=6 and criteriastring='#enumnativevalue'

SET enumsetrawvalue=TRUE
SELECT * from ihrawdata
WHERE samplingmode='rawbytime' and tagname=mytag

Example 15: Retrieve Average Values for Enumerated Sets

SET criteriastring='#enumnativevalue'

SELECT * from ihrawdata
WHERE tagname LIKE Call AND samplingmode=calculated
AND calculationmode=average