# How to query history

**URL:** <https://forum.inductiveautomation.com/t/how-to-query-history/9758>\
**Category:** Ignition\
**Created:** [February 27, 2015, 9:06pm UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758 "2015-02-27T21:06:19Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![palmer\_md](https://avatars.discourse-cdn.com/v4/letter/p/8edcca/32.png) [@palmer\_md](https://forum.inductiveautomation.com/u/palmer_md)\
**Post date:** [February 27, 2015, 9:06pm UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/1 "2015-02-27T21:06:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [February 27, 2015, 10:40pm UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/2 "2015-02-27T22:40:30Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\
**Post date:** [February 28, 2015, 12:00am UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/3 "2015-02-28T00:00:30Z")

</div>

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. 😃

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [March 2, 2015, 2:55pm UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/4 "2015-03-02T14:55:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![palmer\_md](https://avatars.discourse-cdn.com/v4/letter/p/8edcca/32.png) [@palmer\_md](https://forum.inductiveautomation.com/u/palmer_md)\
**Post date:** [March 3, 2015, 7:39am UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/5 "2015-03-03T07:39:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [March 3, 2015, 12:27pm UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/6 "2015-03-03T12:27:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![CPowell](https://avatars.discourse-cdn.com/v4/letter/c/a183cd/32.png) [@CPowell](https://forum.inductiveautomation.com/u/CPowell)\
**Post date:** [March 3, 2015, 6:24pm UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/7 "2015-03-03T18:24:07Z")

</div>

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

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

```auto
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:

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/73da050ce87225924a272aee231ef21a149b5571.proj) (14.8 KB)

---

<div class="post-metadata">

**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\
**Post date:** [March 6, 2015, 3:23am UTC](https://forum.inductiveautomation.com/t/how-to-query-history/9758/8 "2015-03-06T03:23:10Z")

</div>

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.

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/429127deede83be956ea769880cba93c164a4923.proj) (185.7 KB)