MariaDB Historian Manual Query

I want to investigate which tag is flooding the historian.
To start, how do I add a between time clause with fromMilli timestamp:

SELECT *
FROM sqlt_data_1_20240429
WHERE t_stamp BETWEEN ... AND ...
ORDER BY t_stamp DESC
LIMIT 1000

COUNT might be more useful.
Here's a named query from one of my projects that we used to review database usage by tags.

SQL
SELECT 
    a.tagid, 
    COUNT(a.tagid) AS records, 
    b.tagpath
FROM  {schemaName}.sqlt_data_1_{tableYear}_{tableMonth} a
	INNER JOIN {schemaName}.sqlth_te b ON a.tagid = b.id
WHERE a.t_stamp >= (UNIX_TIMESTAMP(:dateTimeStart) * 1000)
	AND a.t_stamp <= (UNIX_TIMESTAMP(:dateTimeEnd) * 1000)
GROUP BY a.tagid
ORDER BY records DESC , a.tagid
LIMIT 0 , 100

(I should probably have used BETWEEN in my query.)

Create a view with a table on it and use a query binding on the data property (with the named query above).

We found that examining an hour at a time once or twice a week was enough to give us a good idea if anything was recording silly amounts of data. We set a notional limit of 200 samples per hour and anything exceeding that was worthy of investigation. It helped reduce our monthly default historian table size by 90%!

2 Likes

Very Cool and Perfecto! Kudos!

I did it also, both in SQL and MySQL


SQL

select
	d.*,
	e.tagpath,
	f.scname
from(
	select
		c.tagid,
		count(c.tagid) as frequency
	from(
		select
			a.*,
			b.tagpath
		from(
			select
				*,
				DATEADD(HH,-5,DATEADD(MILLISECOND, t_stamp % 1000, DATEADD(SECOND, t_stamp / 1000, CAST('1970-01-01' as datetime2(3))))) as ts
			from
				sqlt_data_1_2024_05
		) as a
		left join
			sqlth_te as b on a.tagid=b.id
		where 
			a.ts > dateadd(HH, -1, getdate())
			and
			b.tagpath like '%1%'
	) as c
	group by
		c.tagid
) as d
left join
	sqlth_te as e on d.tagid=e.id
left join
	sqlth_scinfo as f on e.scid=f.id

MySQL

SELECT
	e.*,
	f.tagpath,
	g.scname
FROM(
	SELECT
		d.tagid,
		COUNT(d.tagid) AS frequency
	FROM(
		SELECT
			c.*
		FROM(
			SELECT
				a.*,
				FROM_UNIXTIME(a.t_stamp/1000) AS ts,
				b.tagpath
			FROM
				sqlt_data_1_2024_05 AS a
			LEFT JOIN
				sqlth_te AS b ON a.tagid=b.id
		) AS c
		WHERE c.ts > DATE_SUB(NOW(), INTERVAL 1 HOUR)
	) AS d
	GROUP BY
		d.tagid
) AS e
LEFT JOIN
	sqlth_te AS f ON e.tagid=f.id
LEFT JOIN
	sqlth_scinfo AS g ON f.scid=g.id