ihQuerySettings Table

The ihQuerySettings table contains the current session settings. These settings are applied to all queries you make in a session, unless overridden with a WHERE clause. This table displays settings stored in the provider, and has nothing to do with the data stored in the archives.

The ihQuerySettings table provides a convenient way to display all your session settings. You cannot, however, write or update settings in this table. This table contains only one row with the settings for the current session. The only way to change these parameters is by using the SET statement.

The following table describes the columns of the ihQuerySettings table.
Table 1. ihQuerySettings Table
Column NameData TypeDescription
StartTimeVT_DBTimeStampThe start time of the query. This represents the earliest timestamp for any tag contained in the query.

If no StartTime value is specified, the start time is two hours prior to execution of the query.

EndTimeVT_DBTimeStampThe end time of the query. This represents the latest timestamp for any tag contained in the query.

If no EndTime value is specified, the end time is the time that you execute the query.

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.
Calculated is the default setting.
DirectionVT_BSTRThe direction (Forward or Backward from the start time) of data sampling from the archive. The default value is Forward.
NumberOfSamplesVT_I4Number of samples to retrieve from the archive.

Samples will be evenly spaced within the specified 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 IntervalMilliseconds is used, NumberofSamples 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
  • OPCQOr and OPCQAnd
  • StandardDeviation
  • StateCount
  • StateTime
  • Total
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
The default value is Average.
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 option 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 this value, 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.

If the query result contains more rows than the RowCount value, the Historian OLE DB Provider truncates the extra rows. The truncation is performed last. For instance, if you use ORDER BY in your SELECT statement, the truncation occurs after the rows are ordered.

AlarmTypeVT_BSTRIndicates the alarm type:
  • Alarms
  • Alarm_History
  • Events

ihQuerySettings Examples

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

Example 1: Show All Settings for the Current Session

SELECT * FROM ihquerysettings

Example 2: Show the Selected Session Settings

SELECT starttime, endtime FROM ihquerysettings