How to query history

Ignition
7

I agree with diat150 about double logging data. It is already there, so why not access it.

Here is how I have been accessing the data to show what I want.
I leave the load on the SQL server as that is what it is good at.

First, I created a table with the following custom properties:

start → bind this to the date from my start popup calendar.
end → bind this to the date from my end popup calendar.
lowlimit → display tag values < this limit
highlimit → display tag values > this limit
tagpath → path to the historical tag.
update → I toggle this bit to refresh the query therefore the table.

id → Get the tag id from the sqlth_te database by binding the following SQL query

SELECT id
FROM sqlth_te
WHERE tagpath = '{Root Container.Table.tagpath}' AND retired is NULL

tables -->Based on the start / end properties, get all the table names where the data is stored by binding the following SQL query.

SELECT ISNULL(
	/* Get the table names from the partions. */
	(SELECT TOP 100 pname as pname
	FROM sqlth_partitions
	/* Convert the start property to unix time for comparison */
	WHERE start_time/1000 > DATEDIFF(SECOND,'19700101','{Root Container.Table.start}')
		/* Convert the end property to unix time and add a month to it for comparison */
		AND end_time/1000 < DATEDIFF(SECOND,'19700101',DATEADD(MONTH,1,'{Root Container.Table.end}'))
	ORDER BY pname),
	/* Ok, you are in the current month, so get that table. */
	(SELECT TOP 1 pname as pname
	FROM sqlth_partitions
	ORDER BY end_time DESC))

I partition my history on the month. If you do something different, change the “DATEADD(MONTH” to what you use.

query → This is the query I want to table to return. I get this from my history query script.

Query script:

def query(event):
	id = event.source.getPropertyValue('id')
	tables = event.source.getPropertyValue('tables')
	limit = event.source.getPropertyValue('limit')
	highlimit = event.source.getPropertyValue('highlimit')
	lowlimit = event.source.getPropertyValue('lowlimit')
	start = event.source.getPropertyValue('start')
	end = event.source.getPropertyValue('end')

	
	for row in range(tables.rowCount):
		table = tables.getValueAt(row,0)
		if row == 0:
			query = "SELECT DATEADD(SECOND,((t_stamp/1000) + DATEDIFF(SECOND,SYSDATETIMEOFFSET(), GETDATE())), '19700101') as t_stamp, floatvalue as pv FROM " + table + " WHERE tagid = " + str(id) 
			query +=  " AND t_stamp/1000 > (DATEDIFF(SECOND,'19700101', '" + start + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
			query +=  " AND t_stamp/1000 < (DATEDIFF(SECOND,'19700101', '" + end + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
			if highlimit != '':
				query += " AND floatvalue > " + highlimit
			if lowlimit != '':
				query += " AND floatvalue < " + lowlimit
		else:
			query = "UNION ALL SELECT DATEADD(SECOND,((t_stamp/1000) + DATEDIFF(SECOND,SYSDATETIMEOFFSET(), GETDATE())), '19700101') as t_stamp, floatvalue as pv FROM " + table + " WHERE tagid = " + str(id) 
			query +=  " AND t_stamp/1000 > (DATEDIFF(SECOND,'19700101', '" + start + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
			query +=  " AND t_stamp/1000 < (DATEDIFF(SECOND,'19700101', '" + end + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
			if highlimit != '':
				query += " AND floatvalue > " + highlimit
			if lowlimit != '':
				query += " AND floatvalue < " + lowlimit

	query += " ORDER BY t_stamp DESC"
	
	event.source.setPropertyValue('query', query)

Basically, it builds the sql query that I want to run. I only need ‘floatvalue’ for my needs.
So fairly easy and straightforward…

I exported a History Window so you can take a look at it.
Ignition V7.5

Cheers,
Chris
History Window.proj (14.8 KB)

1 Like