SET Variables

The following table outlines the supported SQL variables and settings that you can use in a SET statement. If you do not change any variables using the SET statement or a WHERE clause in your SELECT statement, the Historian OLE DB Provider assumes default session variables. You can apply any of the variables described in the following table to the current session. In turn, these settings are used when retrieving information from the Historian database tables. SET variables persist from statement to statement.

Some session variables that you define with the SET statement accept abbreviations. You must type at least the abbreviation for the statement to work. For instance, for the CalculationMode setting you can enter the abbreviation Interp for the Interpolated setting. As a minimum, you have to at least enter the letters Interp as the abbreviation for the CalculationMode. The accepted abbreviations are highlighted in bold in the following table.
Table 1. SET Statement Variables
VariableDescription
StartTimeA valid date and time string, such as:
  • StartTime = '14-sep-200111:00:00'
  • StartTime = Now -1h
  • StartTime = '02/01/199811:00:00'
  • StartTime = {ts '2002-06-20 15:34:08'}
  • StartTime = '7/12/201112:03:16.100000'
Default Setting: Two hours prior to execution of the query.
EndTimeA valid date and time string, such as:

EndTime = '14-sep-200112:00:00'

Default Setting: The current time that you execute the query.
SamplingModeString that represents the mode of sampling data from the archive:
  • CurrentValue
  • Interpolated
  • InterpolatedtoRaw
  • RawByTime
  • RawByNumber
  • Calculated
  • Lab
  • LabtoRaw
  • Trend
  • TrendtoRaw
  • Trend2
  • TrendtoRaw2
  • RawByFilterToggle
Default Setting: Calculated
DirectionString that represents the direction of data sampling from the archive, beginning at the start time. Direction applies to the RawByTime and RawByNumber sampling modes:
  • Forward
  • Backward
Default Setting: Forward
NumberOfSamplesAny positive integer that represents the number of samples from the archive to retrieve. Do not enter a thousands separator. For example, enter 1000 and not 1,000.

Samples are evenly spaced within the time range defined by start and end times for most sampling modes. For the RawByNumber sampling mode, the NumberOfSamples attribute determines the maximum number of values to retrieve. For the RawByTime sampling mode, the NumberOfSamples is ignored.

Default Setting: 0 (use IntervalMilliseconds)

IntervalMillisecondsAny positive integer that represents the interval (in milliseconds) between returned samples.

For example:

  • If you run a query with 'IntervalMilliseconds = 100', it returns samples in 100-millisecond intervals.
  • If you run a query with 'IntervalMilliseconds = 100micro', it returns samples in 100-microsecond intervals.
Default Setting: 60000 (one minute)
CalculationModeThe CalculationMode column only applies if the SamplingMode is set to Calculated. It represents the type of calculation to perform on archive data:
  • Average
  • StandardDeviation
  • Total
  • Minimum
  • MaximumCount
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • MinimumTime
  • MaximumTime
  • Count
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
Default Setting: Average
FilterTagA valid tagname used to define the filter, if specified. For example, a FilterTag might be defined as:

FilterTag = 'SimulationString00001'

Only a single tag ID can be specified in the FilterTag. Wildcards are not supported. FilterTag is used in conjunction with FilterValue, FilterComparisonMode, and FilterMode.

Default Setting: An empty space (meaning FilterTag is not used)

FilterModeString that represents the type of time filter:
  • ExactTime
  • BeforeTime
  • AfterTime
  • BeforeAndAfterTime

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. FilterMode is used in conjunction with FilterValue, FilterComparisonMode, and FilterTag.

Default Setting: BeforeTime

FilterComparisonModeString that represents the type of comparison to be made on the filter comparison value:
  • Equal
  • EqualFirst
  • EqualLast
  • NotEqual
  • LessThan
  • GreaterThan
  • LessThanEqual
  • GreaterThanEqual
  • AllBitsSet
  • AnyBitSet
  • AnyBitNotSet
  • AllBitsNotSet
If FilterTag and FilterComparisonValue are supplied in the SET statement, time periods are filtered from the results where the filter condition is False. FilterComparisonMode is used in conjunction with FilterValue, FilterMode, and FilterTag.

Default Setting: Equal

FilterExpressionAn expression which includes multiple filter conditions. FilterExpression can be used instead of FilterTag, FilterComparisonMode and FilterValue.

FilterExpression = 'BatchID=B1'

While using FilterExpression, the expression is passed within single quotes, and for complex expressions we write the conditions within a parenthesis. There is no maximum length for FilterExpression.
FilterValueString that represents the value with which to compare the filter tag to determine the appropriate filter times. Wildcards are not supported. Do not use a comma for the thousands separator.

For example, a sample FilterValue setting might be:

FilterValue = 'ABCD-1086031382099'

The FilterValue is used in conjunction with FilterComparisonMode, FilterMode, and FilterTag.

Default Setting: An empty space (meaning filtering is not used)

TimeZoneString that represents the type of time zone that should be applied to timestamps:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)

For example, an explicit bias number of 300 represents 300 minutes from GMT.

Note: Time zones are not supported on Windows 9x computers.

Default Setting: Client

DaylightSavingTimeFlag that indicates whether Daylight Saving Time logic should be applied to timestamps:
  • True
  • False
Default Setting: Date and time settings in your Windows Control Panel
RowCountPositive number that indicates the maximum number of rows that can be returned. A listing of 0 indicates there is no limit to the number of rows returned.

Default Setting: 5000