What is the simple way to modify a piece of data from basically an hour ago in a historian tag?
Is it to use the MSQL query techniques from those videos?
Or are there separate rules for historized tags?
edit: the solution on how to sort of surgically go in and change one value in the historian tag was provided and appreciated, then I realized that I actually need to do this update 3 times a day, so I revised to unsolved though technically the first inquiry was solved.
like data?
i.e. a 0.1 to a 1.1? if your attempt is as simple as that, you can always write and UPDATE query statement.
If your attempt to change data is deeper, like changing from an int value to a double value, a cell updates needs to be done and all data must be from the same type.
You can always check if trying to understand db tables Ignition Auto-Generated SQL Tables - Ignition User Manual 8.0 - Ignition Documentation (inductiveautomation.com)
Thanks, I had hoped it would be as simple as Update query.
Thanks for the links as well, much appreciated.
If your tag is using “Analog” deadband style, then a simple update may not be enough. That style saves to the database using an algorithm that depends on the previous points saved. The line is reconstructed on query with the assumptions about how adjacent points are related. Modifying a data point without re-evaluating and possibly inserting more data is likely to throw off queries around the modified point.
Good point. As far as I know, the algorithm you mention is used to deploy data and perform interpolations and things like that.
However for storing, it will be stored based on the sample mode.
But I totally agree that INSERT should not be done under any circumstance.
I disagree. INSERT may be necessary depending on the new values of an UPDATE.
under which circumstance would you need to necessarily insert rows? I feel that like manipulating samples timestamps
The OP wants to modify history. A single UPDATE of a value that is using analog deadband mode will change the slope of interpolation on both sides of that point, not just for the period following. And may result in query returns that cannot happen naturally.
Thanks, we are using auto mode I found out. I am going to recommended a switch to discrete.
Shared the link to this post.
If I understand correctly, you are saying that if I use insert, it adds the row and will keep the analog interpolation smooth.
If it is discrete, using an update to modify the desired cell is best since discrete doesn’t have the interpolation.
This particular cell is just like a user comments at the wrap up of a timed event.
This row’s timestamps is periodic, and this one happens at the same time daily.
Thanks very much for help and concern.
No, I'm saying that you may need an insert along with the update to keep the interpolation consistent. You'll have to do the math to determine this on a case by case basis.
It is best to determine what is causing the problem with the original recording and fix it there.
Just one more reassurance that I’m doing the right thing by always sticking with Discrete interpolation. 
Thanks for clarification
How do I UPDATE the value of the historian tag at 3pm yesterday?
Data is in sql_data_x_x_x
I don’t know how to specify the where condition to update the value.
I think I need like:
Update
sqlt_data_x_x_x(tablename I have to get the month and point at the sqlt data with that date)
set
comment(column name) = string (whatever I bind or parameter)
where
(this is the part I have no clue about)
How do I sanitize user comments for entry into my database to show on reports?
please correct me if I am wrong or a simpler way exists
I think I need to do this differently.
The sqlt_data_x_x_x table tstamp is to squirrelly for me to work with.
I shall make a table of year, month, day of month, shift, and comment.
Then when they make a comment, I do an insert to the db.
I need a script to call the query
param1= system.date.getyear
param2= system.date.getmonth
param3= system.date.getdayofmonth
param4=shifttag (set on the page I am making)
param5=comment (also a tag set on the page I am making
params ={param1,param2,param3,param4,param5}
system.db.runNamedQuery(“Project Name”, “queryname”, params)
example query
insert into tablename
(year, month, dayofmonth, shift, comment)
values
(yearparam,monthparam,dayofmonthparam,shiftparam,commentparam)
Then when I go to my report
I do another query?
Is the syntax right, or are those quotes not supposed to be in there?
This code is not working.
path1=["copiedpathfromtag"]
path2=["copiedpathfromtag"]
param1= system.date.getyear
param2= system.date.getmonth
param3= system.date.getdayofmonth
param4= system.readblocking(path1)[0]
param5= system.readblocking(path2)[0]
params ={param1,param2,param3,param4,param5}
system.db.runNamedQuery(testing2, params)
query testing2
insert into testtable
(year, month, dayofmonth, shift, comment)
values
(:year,:month,:dayofmonth,:shift,:comment)
the script portion is saying something like input mismatch expecting dedent, there is an issue with the path to those tags I think
Not sure how to remedy
update- I had a couple syntax errors like not using the [" "] setting my path
and then my indentation was wrong in the script.
now it doesn’t work and it doesn’t throw an error
params ={param1:year,param2:month,param3:dayofmonth,param4:shift,param5:comment}
I think this line is my issue
I don’t know how to write it.
this didn’t work either
params ={"year":"param1","month":"param2","dayofmonth":"param3","shitf":"param4","comment":"param5"}
Tried
params ={"year":"param1","month":"param2","dayofmonth":"param3","shitf":"param4","comment":"param5"}
system.db.runNamedQuery("testing2", params)
still isn’t working
query is titled testing2
INSERT INTO ThisQuery
(year, month, dayofmonth, shift, comment)
VALUES
(:year,:month,:dayofmonth,:shift,:comment)
I have parameters made. types are values, names are the ones above without the colons, datatypes are int4 and one string
my script is simple now for testing
params ={"year":2012,"month":6,"dayofmonth":8,"shitf":4,"comment":"test"}
system.db.runNamedQuery("testing2", params)
I tried using the gateway scope for a while, then I realized this is project scope so I don’t need that.
Not sure what I am doing wrong.
Got an error message? I can take a couple of guesses as to what's wrong, but if there's an error message, that would be a bit more helpful.
Where are you testing this from? The Designer? The Gateway? Either option should have a console to check.
See this this thread for unix time, both SQL and MySQL. You must have edited one of your questions, you had earlier asked how to do it.
See this for queries and parameters, don't forget the manual is awesome!:
https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery
Thanks for the help.
IMO, There should be an error thrown if you use system.readblocking.
I needed to use system.tag.readblocking(path1)[0].value
so that it knew the path, which path, and to use the value of the path
it is finally working
query testing2
INSERT INTO ThisQuery
(year, month, dayofmonth, shift, comment)
VALUES
(:year,:month,:dayofmonth,:shift,:comment)
script event on mouse button up
path1 = ["copiedpathofinttag"]
path2 = ["copiedpathofstringtag"]
param1= 2012
param2= 6
param3= 8
param4= system.tag.readBlocking(path1)[0].value
param5= system.tag.readBlocking(path2)[0].value
params ={"year":param1,"month":param2,"dayofmonth":param3,"shift":param4,"comment":param5}
system.db.runNamedQuery("testing2", params)
my project contains both the script and the button so I didn’t need the gateway scope in the runnamedquery
I have many tabs of the manual and the videos open.