Querying Historical Data with the SQL Query Engine

After the SQL Query Engine stored procedures are registered in your SQL Server database, you can query Data Historian using T-SQL from any connected SQL client. This topic walks you through common query patterns using plausible sensor data from a manufacturing plant.

Discovering Available Tags

Before querying data, use QE_GetAllPointNames to see which historian tags are available and review their metadata.

Example—return all available tags:

The result includes columns such as PointName, Path, DisplayName, EngineeringUnits, RangeLow, and RangeHigh, which you can use to identify the correct tag names for subsequent queries. Pass the value of the PointName column as @pointName in the queries that follow.

Example—filter to tags that contain "Temperature" in their path:

Querying Raw Historical Data

Use QE_GetHistoryRawModified to retrieve the unprocessed samples logged by Data Historian for a given tag and time range. This is equivalent to reading the raw archive data for that tag.

Example—retrieve all raw samples for a temperature sensor over a one-hour window:

The result set returns one row per logged sample, with columns PointName, Timestamp, Quality, and Value. The Quality column reports a status code for each sample, where 0 means Good. Learn more

If the data type of the tag is known, use a typed variant for better performance. For example, to retrieve the same data as double-precision floating-point values:

Querying Aggregated Historical Data

Use QE_GetHdaAnalog to retrieve aggregated values for a numeric tag over a time range, divided into equal-length time buckets. This is useful for generating trend data, computing hourly averages, or reviewing minimum/maximum values for process monitoring.

Example—compute the hourly furnace temperature aggregates over a 24-hour period:

The @resamplingInterval parameter specifies the length of each time bucket, either as a number of milliseconds or as a time span such as 01:00:00. A value of 3600000 produces one result row per hour. Each row holds every aggregate at once: minimum, maximum, average, time-weighted average, time-weighted total, interpolated, last, delta, range, sum, and count. Each aggregate has its own group of columns, such as MINValue, MAXValue, and AVG_Value, with a matching Status column. There is no parameter for choosing an aggregate; select the columns you need from the result. For the full list of columns, see SQL Query Engine Stored Procedures.

Querying Multiple Points in One Call

Use QE_GetHdaAnalog5 to retrieve the same aggregate values for up to five points in a single result row per time bucket. This reduces the number of round trips to Data Historian when your report or analysis needs statistics for several points over the same time range and interval. The columns for each point carry its position as a suffix, such as MINValue1 for the first point and MINValue2 for the second.

Example—retrieve a full statistical summary of flow rate in hourly buckets over a production day:

Querying Boolean Tag Data

For tags that log Boolean values, use QE_GetHdaBool or QE_GetHdaBool5.

Example—retrieve hourly state summary data for a pump running indicator:

Combining Historian Data with SQL Data

Because the stored procedures return standard result sets, you can load the results into a temporary table and join them with relational data already in SQL Server. The following example retrieves hourly temperature aggregates, keeps the average from the AVG_Value column, and joins it with a shift schedule table to label each hour by shift.

Example: