How to query history

I have a customer who asked for what I first thought would be a simple query, and after attempting to do it, turns out seems quite hard. I’m hoping that there is a simple solution that I’m overlooking.

They are logging a pressure on a vessel, and they have an alarm at 0.5 psi, but the would like to have a page that allows them to look at some other low points for the day. For example, the want a screen that has a start date and an end date entered on the screen, they also have a pressure value, say 2psi. From this they want to query the history to see how many times they fell below 2psi between the dates. In the database, its quite simple using a query but from ignition there do not seem to be any tools that allow this. I cant query the database directly without knowing what tables to look at, and the dates might span a month (last day of one month to the first day of the next) and that means is will be in two different tables.

I even tried to just pull up a table with all the data between the dates and then try to extract the needed data from the dataset that was generated. I thought this would be the simplest, but I’m finding it hard to query the dataset. I’ve been able to highlight the data with the mapped background function, but I cant seem to find a way to “hide” the other data or pull out the data with highlighted cells.

I’m pretty sure someone will have a simple solution and I’ll feel dumb for not thinking of it.

Michael

The SQLTag Historian is good for logging data that will be put in charts.

Transaction Groups are good for logging and querying specific data.

Python scripting is good for logging and querying data that requires complicated logic.

Sounds like you might want to use transaction groups to log and query the data. Then the data will be in a format that you can query easily.

You could use the system.tag.queryTagHistory Python function to query the SQLTag Historian for the time range of data and then use Python scripting to filter out the data that you don’t need.

Best,

2 Likes

there was talk a while back of Inductive creating some stored procedures to handle these situations better. Ive never had a chance to try and come up with anything myself.

Transaction groups are nice, but when you have a few hundred thousand tags that are being stored, I cant imagine trying to handle something like that with transaction groups. besides, that data is already in the database, you shouldnt have to duplicate it. :smiley:

This also sounds like a classic alarm application. You can set up an alarm on the tag so that if it drops below a setpoint an alarm is activated. Even if you don’t use the alarm for anything, at least you have a good history or when the pressure dropped (and recovered). I use a lot of alarms for that purpose.

As I noted earlier, they already have an alarm, they want to have a second comparison that can be anything that the operator chooses and look so see how often the value is below that setting.

Oh, my bad. I missed that the first time through. In that case then querying the history and sifting through with a Python script like Nick suggested would probably be the best solution. But make sure you pay careful attention to how you are pulling that history back with the system.tag.queryTagHistory() function. Depending on how you have the history settings on the tag set up and how you pull the data back (min/max, average, etc…) and what the history window is your answer could possibly change… Just keep that in mind.

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

I worked on this for a while and what I came up with seems to work pretty well. It could easily be turned into a function and called in scripting and just pass in the tagpath, a string that is a start date, and a string that is an end date. This is for sql server.

shortcomings that I see.

  1. I noticed some of the monthly partitions have a very slight amount of data from the previous/next month. Its possible that you could miss this data. Now that i think about it, you could easily just add a month to the end month, and subtract a month from the start month and it would cover this scenario.

  2. if you changed your tags datatype, the script wont pick it up. Its going to use the datatype for the last tag entry for the tagpath. Maybe this is something that someone smarter than me can come up with!

  3. Id like to be able to pass in a list of tags. Maybe someone much smarter than me can modify this to use a list of tags and have it pivot the data so you could slap this in a chart or table and have the timestamp on the left and the tagvalues to the right in individual columns.

  4. some error checking could be done to verify startdate is less than end date.

  5. I just noticed that booleans or int tags that represent booleans are more of a sawtooth form than square in a classic chart. Not sure what to do about that. Probably not a big issue since I think most people would just use the easy chart. This is more for exporting data or working with the data in a dataset IMO.

import datetime
drv = '2' # This is the sqlte drv partition setting usually 1, in my case 2
# Here we take a tagpath and collect the associated tagid's
tagPath = '86101/flow'
tagDoc = system.tag.read(tagPath + '.documentation').value # used as column name
tagQuery = "SELECT id FROM sqlth_te where tagpath = '%s'" % tagPath
tagQueryRes = system.db.runQuery(tagQuery)
tagIdList = ''
for row in tagQueryRes:
	tagIdList = tagIdList + str(row[0]) + ','
tagIdList = tagIdList[:-1]
#here we determine the tag datatype to use in the query
tagDataTypeQuery = "SELECT top 1 datatype FROM sqlth_te where tagpath = '%s' order by id desc" % tagPath
tagDataTypeRes = system.db.runQuery(tagDataTypeQuery)
tagDataType = ''
if tagDataTypeRes [0] [0] == 0:
	tagDataType = 'intvalue'	
elif tagDataTypeRes [0] [0] == 1:
	tagDataType = 'floatvalue'
elif tagDataTypeRes [0] [0] == 2:
	tagDataType = 'stringvalue'
#here, we look at the startDate and EndDate strings and do conversions to use thru the script. I used popup calenders
startDate = datetime.datetime.strptime(event.source.parent.getComponent('Start').formattedDate, '%Y-%m-%d %H:%M:%S')
endDate = datetime.datetime.strptime(event.source.parent.getComponent('End').formattedDate, '%Y-%m-%d %H:%M:%S')
startWhereClauseAdd = " and cast(dateadd(hour, -6, dateadd(s,t_stamp/1000,'1970-01-01 00:00:00')) as datetime) >= '%s' "  % startDate
endWhereClauseAdd = " and cast(dateadd(hour, -6, dateadd(s,t_stamp/1000,'1970-01-01 00:00:00')) as datetime) <= '%s'" % endDate
whereClause = ' where tagid in (%s) and dataintegrity = 192' % tagIdList
# here we build the table names that need to be used to collect the data
tableList = []
for y in range (startDate.year, endDate.year + 1):
    if y == startDate.year:
        for m in range(startDate.month, 13):
            tableList.append('sqlt_data_'+ drv + '_' + str(y) + '_' + str(m).zfill(2))
    elif y not in (startDate.year,endDate.year):
        for m in range(01, 13):
            tableList.append('sqlt_data_'+ drv + '_' + str(y) + '_' + str(m).zfill(2))
    elif y == endDate.year:
        for m in range(01, endDate.month + 1):
            tableList.append('sqlt_data_'+ drv + '_' + str(y) + '_' + str(m).zfill(2))
#here we check to make sure that the tables exist, if they dont, we throw them out. i.e a user selected a date that is before history was available
validTableList = []
validTableListLastRow = ''
for row in tableList:
    tableExistsQuery = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'%s'" % row
    tableExistsQueryRes = system.db.runQuery(tableExistsQuery)
    if len(tableExistsQueryRes) > 0:
    	validTableList.append(row)
    	validTableListLastRow = row
validTableListLength = len(validTableList)
#here we start building the final query to be used
finalQuery = ''
validTableListIdx = 1
for row in validTableList:
	queryTemplate = "SELECT dateadd(hour, -6, dateadd(second, [t_stamp]/1000, '1970-01-01')) as 't_stamp', " + tagDataType + ' as ' + "'" + tagDoc + "'" + " FROM " + row + whereClause	
	if row != validTableListLastRow and validTableListIdx == 1 :
		finalQuery =queryTemplate + startWhereClauseAdd +  ' Union '
	elif row != validTableListLastRow and validTableListIdx > 1 :
		finalQuery = finalQuery + queryTemplate +  ' Union '
	elif row == validTableListLastRow and validTableListIdx ==1  :
		finalQuery = finalQuery + queryTemplate + startWhereClauseAdd + endWhereClauseAdd + ' order by t_stamp'
	elif row == validTableListLastRow and validTableListIdx > 1  :
		finalQuery = finalQuery + queryTemplate + endWhereClauseAdd + ' order by t_stamp'
	validTableListIdx = validTableListIdx + 1
print finalQuery
finalQueryRes = system.db.runQuery(finalQuery)
#I sent my data to a chart component
event.source.parent.getComponent('Chart').Data = finalQueryRes
	

I also attached a window that has everything setup. just put in a tagpath, as long as you are using default tagprovider and database on your project.
sqltaghist.proj (185.7 KB)

1 Like