Troubleshooting Ignition Tag History Queries in MySQL

Why does a SELECT on sqlth_te return only one row?

Start where the query lands. A SELECT * FROM sqlth_te WHERE tagpath = '...' goes from the client, through the database connection, to one table: the tag entity registry. That table holds no samples. It maps each historized tag path to an integer ID plus metadata such as data type and when the entry was created or retired. One tag normally produces one row. A tag whose data type changed, or that was retired and re-added, produces a few more. One row back means the query worked, but it read the index instead of the data.

The historian writes samples into separate data tables that are partitioned by time. To reach those samples, a query must resolve the path to the integer ID, find which partition tables cover the time range, read those tables, and then apply the historian's storage rules. Each check below follows the query one hop further along that path.

Check 1: Does the tag path resolve to the right ID?

Confirm the first hop before you open any partitions. Run the sqlth_te query and record the id, the data type, and the created and retired columns.

Path mismatches come from three places:

• Binding substitution. The path in this case embeds {Root Container.Text Field.text} twice. In a SQL Query property binding, those references are replaced by the Text Field's current value before the statement reaches the database. An empty field, or trailing whitespace in the field, sends a path that matches nothing. Read the Text Field value at runtime and paste the fully resolved string into a database query browser to test it.
• Provider prefix. The stored path does not include the tag provider in square brackets. The provider is tracked through sqlth_drv. Remove any [provider] prefix from the WHERE clause.
• Case. The historian normalizes stored paths, so compare case-insensitively (WHERE LOWER(tagpath) = LOWER('...')) or copy the exact string from the table.

Check 2: Which partition tables hold the samples?

Follow the ID into the data tables. The historian creates a new data table for each partition period. The default period is monthly, so a query across several months has to read several tables. Do not guess a table name. Read the list:

SELECT pname, drvid, start_time, end_time
FROM sqlth_partitions
ORDER BY start_time;

start_time and end_time are epoch milliseconds. Choose every partition whose range overlaps your query window, then join on the ID from Check 1:

-- replace sqlt_data_X with the pname values from sqlth_partitions
SELECT te.tagpath, d.t_stamp, d.intvalue, d.floatvalue,
       d.stringvalue, d.datevalue, d.dataintegrity
FROM sqlth_te te
JOIN sqlt_data_X d ON d.tagid = te.id
WHERE te.id = <id from Check 1>
ORDER BY d.t_stamp;

A UNION ALL across partitions is required for any multi-month window. This is the first reason hand-written SQL against the historian breaks down: the table list changes every partition period, so a query that is correct today returns nothing after the next rollover.

Check 3: Why do the raw rows look sparse or wrong?

Rows are present, but the trend looks wrong. That comes from how the historian stores data, not from a lost sample.

• Value column by type. Only one of intvalue, floatvalue, stringvalue, datevalue is populated, based on the data type in sqlth_te. A humidity float reads NULL in intvalue.
• Deadband and on-change storage. The historian writes a row only when the value moves beyond the tag's deadband. A flat signal produces hours without rows. Seeing "no row" means "unchanged" only if the scan class was executing during that period. That execution record is in sqlth_sce.
• Timestamps. t_stamp is epoch milliseconds in UTC. Convert it with in MySQL, and account for the server time-zone offset.
• Quality. dataintegrity carries the quality code. Filter out bad-quality rows or display them separately. Do not average them in.
• No seed value. A raw window query does not return the last value stored before the window start. A chart therefore starts blank until the first change inside the window.

Handling interpolation, seed values, scan-class gaps, quality, and partition joins in SQL is a lot of effort to reproduce behavior the historian already provides. That leads to the resolving branch.

How do I pull the tag's history through the historian instead?

Route the request through the historian's query engine. The engine resolves the path, walks the partitions, applies deadband-aware interpolation and seed values, and returns a dataset. There are two entry points.

1. Build the path string from the component value and remove whitespace.
2. Call the historian:

   sensor = event.source.parent.getComponent('Text Field').text.strip()
   path = 'a_name/goldtest7_7simulator/sensor ' + sensor + '/sensor ' + sensor + '/humidity'
   
   end = system.date.now()
   start = system.date.addHours(end, -24)
   
   ds = system.tag.queryTagHistory(
       paths=[path],
       startDate=start,
       endDate=end,
       returnSize=-1,          # values as they changed
       aggregationMode='LastValue',
       returnFormat='Wide')
   
   event.source.parent.getComponent('Table').data = ds

3. For a binding, open the target's dataset property and select the Tag History binding type. Add the tag, then make the path indirect so the Text Field value supplies the sensor segment. Set the date range and the return size.
4. Remove the SQL Query binding that targeted sqlth_te so two bindings do not write to the same property.

How do I verify the history query is correct?

1. Type a known sensor name into the Text Field. Confirm the dataset's column header matches the resolved tag path. A header with a blank sensor segment means the substitution failed before the request left the client.
2. Pick a narrow window, such as one hour, and count the rows returned with returnSize=-1. Run the Check 2 partition query for the same ID and window. The counts should match, apart from any seed row at the window start.
3. Spot-check three timestamps. Convert t_stamp with and compare the value to the same instant in the dataset. A consistent whole-hour offset is a time-zone setting, not missing data.
4. Change the live tag value past its deadband. Wait one scan period, re-run the query, and confirm the new sample appears as the last row with good quality.

FAQ