Query Internal Tag Historian

Ignition
,
1

How do I query the internal tag historian SQLite database, if possible? I have tag history enabled for a flow and would like to use a query to do a summation of the last 7 days’ final flows.

What is the internal .db file location?

2

Use, a Historical Tag Binding, or system.tag.queryTagHistory on the correct provider.

1 Like
3

What about Vision?

4

I want to learn more about this too if examples are povided

5

I don’t know about bindings in vision, but system.tag.queryTagHistory and queryTagCalculations should be available.

@zacharyw.larson What do you want to know ?

6

In this instance it is the same for either perspective or vision.

7

Is this something like what you are looking for?

endTime = system.date.now()
startTime = system.date.addDays(endTime, -7)
dataSet = system.tag.queryTagHistory(paths=['[default]New Tag4'], startDate=startTime, endDate=endTime)
sumFlow = 0
for row in range(dataSet.getRowCount()):
	sumFlow += dataSet.getValueAt(row,1)
print sumFlow
8

queryTagCalculations seems like an easier way to do this:

dataset = system.tag.queryTagCalculation(paths, rangeHours=-24*7, calculations=["Sum"])
sumFlow = dataset.getValueAt(0, 1)

With the added benefit that you can query multiple calculations at the same time quite easily:

dataset = system.tag.queryTagCalculation(paths, rangeHours=-24*7, calculations=["Sum", "Average"])
sumFlow = dataset.getValueAt(0, 1)
averageFlow = dataset.getValueAt(0, 2)
9

@josborn @pascal.fragnoud

I actually ended up doing it with a couple of different parameters and bindings.

  1. Parameter “LastNight” Expression binding: addMinutes(midnight(now()),-1)
  2. Parameter “WeekAgo” Expression binding addWeeks({LastNight},-1)
  3. Parameter “LastWeek_Data” Tag History binding using historical date range and the two dates calculated in 1 and 2 and a fixed sample size interval of 24 hours.
  4. Parameter “LastWeek_Total” Expression binding sum({LastWeek_Data},'output')
10

Well I made a transaction group for infeed count, outfeed count, and machine state for example.
I can make a query of that table.

but is there a faster way to query the historian instead?
I think to be much faster with getting data from the historian rather than transaction groups and queries is what I meant I wan to learn.

11

To each their own, this was not "hard" by any means and allows you to manipulate the dataset in any way you want.

12

The 2 methods they’re talking about are ways to query the historical data collection. I presume you’re counting in the PLC and collecting the count on some interval with a SQL Bridge transaction. You are probably doing it in a good way for what you’re trying to accomplish.

You could use the historical collection if you were doing historic collection of the state of a bit that goes high every time you get a count but I would prefer the SQL Bridge method because you are less likely to miss a count when the counting is happening on the PLC and you’d be recording fewer records to record the same data. It is good to consolidate round trips to the database and to get your data in real time (PLC) vs through a comms driver.

3 Likes