Example SQL query to poll tag history using external database

General Discussion
1

Hi,

i am using MS SQL as tag historian with ignition. I am trying to query the SQL database using external software. I can see the explanation of table format and structure provided here.
Ignition Database Table Reference - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

But, i am wondering if anyone can share example queries that can simply read one tag over time range.

2

While this is technically possible, I’d recommend against it if you can help it. The reason being that your data is likely to be split across a variable number of tables. Assuming you know which tables you’re looking for ahead of time (based on the documentation you cited), I’d probably do something like this:

Given you know the table(s) you’re pulling from (and the tag datatype), it’s not terribly complex.

CREATE FUNCTION GetSingleIntTagHistory 
(
	@tagPath varchar, 
	@start bigint,
	@end bigint
) 
RETURNS TABLE 
AS
RETURN 
(
	SELECT t_stamp, intvalue
	FROM sqlt_data_1_2022_05
	JOIN sqlth_te ON sqlth_te.id = sqlt_data_1_2022_05.tagid
	WHERE tagpath = @tagPath
		AND t_stamp BETWEEN @start AND @end
)

I wouldn’t know how to do it without knowing the table names ahead of time, but I think it’s possible.