Editing historian tag data

Ignition
21

You may want to read up again on readBlocking:
https://docs.inductiveautomation.com/display/DOC80/system.tag.readBlocking

You can read multiple tags at once.

1 Like
22

Fwiw, I would avoid generic variable names such as 'param1' etc. As it makes diagnosing and finding errors far more difficult, especially when you come back to it after a year, better to use descriptive variable names e.g. year, month, day, intTagPath, stringTagPath

2 Likes
23

Also, consider not breaking up your date into separate parts. Just use a datetime column. It’ll make correlating your annotations with history data much easier. Especially if you want to display the annotations with something like my NoteChart module.

4 Likes
25

How do you easily work with one column date with script language, the expression language, and the tstamps epoc server variations?

I didn’t know how to work with those all, but I know how to pull out the parts I need sometimes.
If I can learn how to work better with the different parts, I would like to use one column for dates.

Thanks for help

26

I thought the page was informative. Maybe this example will help:

paths = ["[default]Folder/Tag_A","[default]Folder/Tag_B"]
values = system.tag.readBlocking(paths)
print values[0].value #this will be Tag_A
print values[1].value #this will be Tag_B
2 Likes
27

To expand a bit on what @jlandwerlen posted and to help with your need to know which path goes with the value.

When provided with a list of tag paths, system.tag.readblocking will return a list of qualified values where the order matches the order in which the paths were associated.

So as he commented in the code, the value for the tag path provided in index 0 of the paths list will be at index 0 in the values list. In other words once you build the paths list you will always know where the associated value for that path will be.

I often use this to my advantage when I am reading a large number of tags and build a dictionary where the tag path or name is the key, so that I can easily reference the value later in the script.

2 Likes
28

I meant to delete my comment to avoid muddling through the details of how I think about it, and just say thanks for the help.

For me, right or wrong, I think:

few columns in Excel to scale fast
Consistently the 0th
Uniform rows
easy troubleshooting
easy scaling both directions

Incrementing could be unnecessary work for me
Like if I add a machine 13 for my series of 20, I don’t want to make it number 19.
I want it to be 0th.
Or I would have to bump 7 other machines to make it 12.

I think cases can be made both ways. I want to say thanks for helping me though.
I just have such a long experience of looking at that particular page and the writeblocking page, and just being so upset that they had to show list examples and didn’t show single cases for scaling in the manner that I perceive I want to scale.

I usually post proof of concept cases for what I am trying to do. I have to apply it to a bunch of machines. Then I gaffed using param1 as the variable name which is potentially confusing with the use of params.

Counting the items in the list with many tags could be time consuming for me
As far as getting lost in which path is where, I mean like on a Friday before close, I don’t want to be counting over how many the tag is in the a list to figure out the number for multiple tags. Just having the 0th will save me time on that end. If I get the number wrong, I don’t know how long it would take to figure out for example.

I can see a lot of value in the dictionary side. I am always conflicted about giving details, or discussing details. I don’t want to miss opportunities to learn, but I also want to be sure to explain in order to not be misunderstood if necessary. I both don’t want to cut corners nor do extra work.

I think I need to see the way the dictionary is done that the list helps with.
My knee jerk is like, I don’t want to type list, and then try to count or move the index if I add and subtract.

Thanks for the help

Sorry it was was long winded. I get a little anxiety when experts say they do something one way and I am doing it a different way. So I wanted to be sure to show all my concerns that cause me to think it is good to do it a way different.

29

First of all, please don’t think I am saying that your are doing it wrong, IMO if it works then it isn’t really “wrong”. However, there are often more ‘reliable’, ‘performant’ and/or ‘more readable’ ways of doing things. That is what makes best practices, best practices.

You can read each tag individually, it will work, it is not the most performant way to do it.

This is the way I do it, it is not the only way to do it, nor is it probably the “best” way to do it.

So, here is how I do this. At the beginning of any script were I will be working with more than a few tags I build what I call a valueDictionary, if I will be modifying any tags I change them in the in the dictionary and then use that to write them all at the end of the script.

I keep these functions in the global scripting project.

I provide the functions with a list of tag paths and then if they happen to all share a common parent path I also provide that, the end result is a dictioary of values whose key is the tagName/path. If a parentPath is not provided then the full path will be used as the key.

Here is the script for creating the dictionary, I have mine in a shared project script called tagUtils

def createTagValueDict(tagPaths,parentPath):
	'''
	Called to read the current tag values of tagPaths provided in tagPaths, and 
	generate a dictionary where key is the tag name and the value is the read
	tag value.
	
	Arguments:
		tagPaths: List of tag paths to read tag values of
		parentPath: path to a folder containing all of the tags
	'''
	values = system.tag.readBlocking(tagPaths)
	tagValues = {}

	for valueIndex, qualifiedValue in enumerate(values):
		tagValues[tagPaths[valueIndex][len(parentPath):]] = qualifiedValue.value
		
	return tagValues

Here is the script for writing the values out of the dictionary

def writeTagValueDict(values,parentPath):
	'''
	Called to write values in a dictionary to the tags at path "parentPath" + key.
	
	Arguments:
		values: dictionary of values with tagNames as the key
		parentPath: path to the folder containing all of the tags.
	'''
	writePaths = []
	writeValues = []
	
	#if the given parenPath does not end with / then add it.
	parentPath = parentPath if parentPath[-1:] == '/' else parentPath + '/'
	
	for tag,value in values.items():
		writePaths.append(parentPath + tag)
		writeValues.append(value)
	
	system.tag.writeBlocking(writePaths,writeValues)

Usage would look something like the following:

tagPaths = ['parentPath/Tag1','parentPath/Tag2','parentPath/Tag3']
values = shared.tagUtils.createTagValueDict(tagPaths,'parentPath/')

if values['Tag1'] == values['Tag2']:
    values['Tag3'] += 1

shared.tagUtils.writeTagValueDict(values,'parentPath')

That is a very simplified example but hopefully you can see how much more readable values['Tag1'] is than values[0].value

If you would like a more detailed explanation of how this code works I would be happy to DM it to you.

1 Like
30

Convert epoch to datetime, either with a SQL function (T-SQL [MS SQL]):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_ConvertToDateTime] (@Datetime BIGINT, @ConvertToLocal bit)
RETURNS DATETIME2(7)
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetimeSec BIGINT
		   ,@Milliseconds BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())

	SET @AdjustedLocalDatetimeSec = @Datetime / 1000 -- Convert to seconds
	SET @Milliseconds = @Datetime % 1000

    IF @ConvertToLocal = 1
		SET @AdjustedLocalDatetimeSec = @AdjustedLocalDatetimeSec - @LocalTimeOffset;
	
	
    RETURN (SELECT DATEADD(millisecond, @Milliseconds, DATEADD(second,@AdjustedLocalDatetimeSec, CAST('1970-01-01 00:00:00.000000' AS datetime2(7)))))
END;

or a with python (there might be another java-based way that would be more appropriate):

datetime_time = datetime.datetime.fromtimestamp(epoch_time)
OR
import time
time.localtime(epoch)
1 Like
31

I appreciate seeing this code. I would have to go to DMs to understand it enough to use it.
For now, I will probably keep doing the 0th entries.

I favor loop unrolling
I wonder what the time overhead is per system.tag.readblocking() I use

I favor 0th consistency
Your dictionary method circumvents the need to number and you are able to scale easy as well

@nminchin
I didn’t know there was a fromtimestamp() that could translate the epoc time easy
Thanks
The sql function is intimidating

32

Throw this into your script console and run it.

from java.lang import System

tagPaths = []
ret = 0
currentTime = system.date.now()

for i in range(400):
	tagPaths.append('[Client]CurrentTime')
	

start = System.nanoTime()

for i in range(400):
	ret = system.tag.readBlocking(tagPaths[i])
	
end = System.nanoTime()

print '400 singular reads took %.3f ms' % ((end-start) / 1000000.0)

values = []
paths = []

start = System.nanoTime()

for i in range(400):
	values.append(currentTime)
	paths.append('[Client]CurrentTime')
	if i % 5 == 0:
		ret = system.tag.readBlocking(paths)
		values = []
		paths = []

end = System.nanoTime()

print '400 readss in blocks of 5 took %.3f ms' % ((end-start) / 1000000.0)

values=[]
paths = []

start = System.nanoTime()

for i in range(400):
	values.append(currentTime)
	paths.append('[Client]CurrentTime')
ret = system.tag.readBlocking(paths)

end = System.nanoTime()

print '400 reads in 1 block took %.3f ms' % ((end-start) / 1000000.0)

Here were the results on my system:

>>> 
400 singular reads took 16.169 ms
400 reads in blocks of 5 took 3.345 ms
400 reads in 1 block took 0.569 ms
>>>

As the size of the system and load on the gateway changes these results will very, however, reading all tags at once will remain the fastest by a lot.

3 Likes
33

that is cool, I will need to test it later
I will write in excel

“system.tag.readBlocking(”
1-400 columns
“)”

concat the columns, paste, and then see

if I have time end of day, I will try it

34

I dont know when I will get to it, but your method seems to be the way to go

That way I can just list them, and edit them on the fly for fast scaling without worrying about the order

35

I am unable to run project scripts

I want to implement this to speed up my reads and writes.
I can only call project scripts in my perspective page scripts, not on my tag provider.

36

You've lost me, why is the tag provider of concern?

1 Like
37

I guess it wasn’t. Used the list comprehensions and this thread information to fix those scripts. Thanks very much.

How do I know which tagid in the historian is which tag?

I figured out which tagid to fix the value I needed to by checking values in queries.

I wondered is there was an easier way to identify the tagid for the historian.

38

See this for the full explanation: Ignition Database Table Reference - Ignition User Manual 8.1 - Ignition Documentation

TLDR. Just look at the sqlth_te table and find your tagpath’s tagid.

2 Likes
39

Thanks very much.

That is great.

where tagpath like '%speed%'

Using the like keyword really helped too

That page is very informative as well, thanks

1 Like
40

Thanks for the help.
Today, I am able to query the sqlth_te for the id tag with this:
where tagpath like '%speed%'

Then I can find timestamps and convert timestamp with this:

SELECT *
, dateadd( second, t_stamp/1000, CAST( '1970-01-01' as datetime ) ) 
FROM sqlt_data_1_2022_08
where tagid in (578,602,664,722,46)

Today, I can write a case to replace the tagid or do a join to get the tagpath from the sqlth_te.
Thanks for helping me.
I am able to retrieve data from last August today.
Felt great to be able to.

Ignition Database Table Reference - Ignition User Manual 8.1 - Ignition Documentation

This helped again, thanks Brandon1 and Ignition team.