# Editing historian tag data

**URL:** <https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328>\
**Category:** Ignition\
**Created:** [July 28, 2021, 8:46pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328 "2021-07-28T20:46:08Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 28, 2021, 8:46pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/1 "2021-07-28T20:46:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [July 29, 2021, 2:55am UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/2 "2021-07-29T02:55:53Z")

</div>

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)](https://docs.inductiveautomation.com/display/DOC80/Ignition+Auto-Generated+SQL+Tables)

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 29, 2021, 11:59am UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/3 "2021-07-29T11:59:36Z")

</div>

Thanks, I had hoped it would be as simple as Update query.

Thanks for the links as well, much appreciated.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 29, 2021, 12:19pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/4 "2021-07-29T12:19:28Z")

</div>

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.

[https://docs.inductiveautomation.com/display/DOC80/Configuring+Tag+History#ConfiguringTagHistory-DeadbandandAnalogCompression](https://docs.inductiveautomation.com/display/DOC80/Configuring+Tag+History#ConfiguringTagHistory-DeadbandandAnalogCompression)

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [July 29, 2021, 10:21pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/5 "2021-07-29T22:21:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 30, 2021, 12:42am UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/6 "2021-07-30T00:42:48Z")

</div>

> [@jespinmartin1](#):
>
> But I totally agree that INSERT should not be done under ant circumstance.

I disagree. INSERT may be **necessary** depending on the new values of an UPDATE.

---

<div class="post-metadata">

**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\
**Post date:** [July 30, 2021, 1:02am UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/7 "2021-07-30T01:02:34Z")

</div>

under which circumstance would you need to **necessarily** insert rows? I feel that like manipulating samples timestamps

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 30, 2021, 2:02am UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/8 "2021-07-30T02:02:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 12:04pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/9 "2021-07-30T12:04:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 30, 2021, 1:30pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/10 "2021-07-30T13:30:09Z")

</div>

> [@zacharyw.larson](#):
>
> If I understand correctly, you are saying that if I use insert, it adds the row and will keep the analog interpolation smooth.

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.

---

<div class="post-metadata">

**Author:** ![brandon1](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@brandon1](https://forum.inductiveautomation.com/u/brandon1)\
**Post date:** [July 30, 2021, 1:45pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/11 "2021-07-30T13:45:26Z")

</div>

Just one more reassurance that I’m doing the right thing by always sticking with Discrete interpolation. 🙂

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 1:53pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/12 "2021-07-30T13:53:48Z")

</div>

Thanks for clarification

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 2:11pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/13 "2021-07-30T14:11:42Z")

</div>

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:

```auto

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?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 3:48pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/14 "2021-07-30T15:48:58Z")

</div>

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

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

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

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 5:49pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/15 "2021-07-30T17:49:07Z")

</div>

This code is not working.

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

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

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 6:33pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/16 "2021-07-30T18:33:40Z")

</div>

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

```auto
params ={"year":"param1","month":"param2","dayofmonth":"param3","shitf":"param4","comment":"param5"}

```

Tried

```auto
params ={"year":"param1","month":"param2","dayofmonth":"param3","shitf":"param4","comment":"param5"}
		
	system.db.runNamedQuery("testing2", params)

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 7:13pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/17 "2021-07-30T19:13:29Z")

</div>

still isn’t working

query is titled testing2

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

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

---

<div class="post-metadata">

**Author:** ![Paul.Scott](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul.scott/32/4578_2.png) [@Paul.Scott](https://forum.inductiveautomation.com/u/Paul.Scott)\
**Post date:** [July 30, 2021, 7:40pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/18 "2021-07-30T19:40:32Z")

</div>

> [@zacharyw.larson](#):
>
> still isn’t working

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.

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [July 30, 2021, 7:47pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/19 "2021-07-30T19:47:16Z")

</div>

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.

> [@Tag Historian SQL Queries](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/15):
>
> Hi, Thank you very much for the link. I was able to create it with the help of that video, documentation and support. Regards, Kumar

See this for queries and parameters, don't forget the manual is awesome!:  
[https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery)

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [July 30, 2021, 8:02pm UTC](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328/20 "2021-07-30T20:02:40Z")

</div>

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

```auto
INSERT INTO ThisQuery
	(year, month, dayofmonth, shift, comment)
VALUES
	(:year,:month,:dayofmonth,:shift,:comment)

```

script event on mouse button up

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

[Next page](https://forum.inductiveautomation.com/t/editing-historian-tag-data/49328.md?page=2)