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 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.

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 TAGNAME, TIMESTAMP, QUALITY, and VALUE.

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 min/max 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 in milliseconds. A value of 3600000 produces one result row per hour. The result columns are TAGNAME, TIMESTAMP, QUALITY, and VALUE.

Querying Multiple Aggregates in One Call

Use QE_GetHdaAnalog5 to retrieve aggregate values—minimum, maximum, average, time-weighted average, and total— for 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.

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 average temperatures and joins them with a shift schedule table to label each hour by shift.

Example: