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 default session variables are considered. 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. The accepted abbreviations are highlighted in bold in the following table.
Table 1. SET Statement Variables
Variable Description Default Setting
StartTime A 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'
Two hours prior to execution of the query.
EndTime A valid date and time string, such as:

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

The current time that you execute the query.
SamplingMode String that represents the mode of sampling data from the archive:
  • CurrentValue
  • Interpolated
  • InterpolatedtoRaw
  • RawByTime
  • RawByNumber
  • Calculated
  • Lab
  • LabtoRaw
  • Trend
  • TrendtoRaw
  • Trend2
  • TrendtoRaw2
  • RawByFilterToggle
 Calculated
Direction String 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
 Forward
NumberOfSamples Any 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.

 0 (use IntervalMilliseconds)
IntervalMilliseconds Any 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.
 60000 (one minute)
CalculationMode The CalculationMode column applies only 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
Average
FilterTag A valid tagname used to define the filter. For example:

FilterTag = 'SimulationString00001'

You can specify 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.

An empty space (meaning FilterTag is not used)
FilterMode String 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.

BeforeTime
FilterComparisonMode String 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 you enter FilterTag and FilterComparisonValue 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.		 Equal
FilterExpression An expression that includes multiple filter conditions. You can use FilterExpression 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 parentheses. There is no maximum length for FilterExpression.
FilterValue String 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:

FilterValue = 'ABCD-1086031382099'

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

An empty space (meaning filtering is not used)
TimeZone String 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.
Client
DaylightSavingTime Indicates whether Daylight Saving Time logic should be applied to timestamps. Valid values:
  • True
  • False
Date and time settings in your Windows Control Panel
RowCount A number that indicates the maximum number of rows that can be returned. 0 indicates there is no limit to the number of rows returned. 5000