# MariaDB Historian Manual Query

**URL:** <https://forum.inductiveautomation.com/t/mariadb-historian-manual-query/89675>\
**Category:** Ignition\
**Created:** [April 30, 2024, 1:32am UTC](https://forum.inductiveautomation.com/t/mariadb-historian-manual-query/89675 "2024-04-30T01:32:04Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Eugene49](https://avatars.discourse-cdn.com/v4/letter/e/c6cbf5/32.png) [@Eugene49](https://forum.inductiveautomation.com/u/Eugene49)\
**Post date:** [April 30, 2024, 1:32am UTC](https://forum.inductiveautomation.com/t/mariadb-historian-manual-query/89675/1 "2024-04-30T01:32:04Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/7/373c0b92c5b13c118d8eef276970d920b6a97ec2.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [April 30, 2024, 3:51am UTC](https://forum.inductiveautomation.com/t/mariadb-historian-manual-query/89675/2 "2024-04-30T03:51:21Z")

</div>

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

 ![Top 100 tags](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/6/66b31024213a367717ea47938092ff4645c83041.png)

> **SQL**
>
> ```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).

 ![Top 100 page](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/1/2130c4cac1b740c73cdeec83534da5793f99f4b6.png)

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%!

---

<div class="post-metadata">

**Author:** ![Eugene49](https://avatars.discourse-cdn.com/v4/letter/e/c6cbf5/32.png) [@Eugene49](https://forum.inductiveautomation.com/u/Eugene49)\
**Post date:** [April 30, 2024, 5:38am UTC](https://forum.inductiveautomation.com/t/mariadb-historian-manual-query/89675/3 "2024-04-30T05:38:45Z")

</div>

Very Cool and Perfecto! Kudos!

---

<div class="post-metadata">

**Author:** ![Eugene49](https://avatars.discourse-cdn.com/v4/letter/e/c6cbf5/32.png) [@Eugene49](https://forum.inductiveautomation.com/u/Eugene49)\
**Post date:** [May 16, 2024, 10:58pm UTC](https://forum.inductiveautomation.com/t/mariadb-historian-manual-query/89675/4 "2024-05-16T22:58:43Z")

</div>

I did it also, both in SQL and MySQL

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/a/2a772015ee668d1f365cd7d3b9307320ab0d4a88.png)  
SQL

```python
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

```python
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

```