Merge old tag history with rename new tag history

Ignition
1

Ignition Version 8.1.2
I create a tag and config it history. SQL ID is 5051.
Later I rename this tag on 2022-10-19 . SQL ID is 5188.
When I query Chart ,I just see the new tag chart.
I can't query the old tag and rename new tag chart together.
How to merge them together?

1667614117918
1667614117918959×644 92.3 KB

1667614073456
1667614073456943×319 85.3 KB

image
image1058×252 15.8 KB

2

Hi taohongyu88,

Please reach out to support regarding merging old tag history with new tag history. You can reach us here Support Home Page | Inductive Automation

Best,

3

To be fair, merging of tag history due to a rename or move shouldn't require support to help with this. This should really be something that can easily be done by an integrator.

@taohongyu88 you just need to change the tagid of all of the relevant records in your sqlth_data_x tables to the new tag id.
PS. If the above doesn't make sense and you don't know what you're doing in SQL, you could end up destroying all of your tag history. In which case a support call is probably recommended.

E.g.
For:

image
image843×265 20.1 KB

Execute this below to re-map your old history to the new tag:

UPDATE
	sqlt_data_5_2022_11
SET
	tagid = 5262
WHERE
	tagid = 5261

ENSURE YOU HAVE A "WHERE" CLAUSE AND THAT IT IS CORRECT before executing that UPDATE query
Excluding a WHERE clause will re-map all history in that table to your new tag.
Using the wrong WHERE clause may have equally catastrophic consequences

4

I find a new question.
When I update old tag's tagid to new tag's tagid ;
PowerChart show this month's history ,but not show the last month's history?

Power Chart not show the rename tag's last month History? - Ignition - Inductive Automation Forum

5

I assume you mean when you updated history for the old tag's history to point to the new tagid?

6

Yeah Thank you.
In Database -Table-sqlth_te , must Update created time to the old tag's created time.
update sqlth_te set created ='1716865518209' where tagid=7443

7

Hello,

I would like to add a comment here since “Merge History Ignition” finds this thread on search engines.

It is a possible client ask to rename tags in the historian but keep the history. This is a way that you can deal with such a request:

  • Copy the UDTs or tags to JSON and edit the names so that the configuration is identical.
    • To avoid recording new data you must disable any new historical points before creating the tag. Or nminchin’s SQL update will run into an error code due to duplicates.
    • See below for another SQL query that can ignore duplicate entries
  • Go on the database browser and find the new tag id and old tagid as nminchin pointed out “where tagpath like ‘%your tag here%’
  • Then this is the tricky step. Since you will have new history on the newly created point the sql query will find duplicates. This is how you can avoid it:
    UPDATE sqlt_data_5_2022_11 AS old
    LEFT JOIN sqlt_data_5_2022_11 AS new
    ON new.tagid = 5262 AND new.t_stamp = old.t_stamp
    SET old.tagid = 5262
    WHERE old.tagid = 5261
    AND new.tagid IS NULL;
  • If instead you would like to ‘copy’ the history over and not just edit the tagid, then this can be done:

INSERT INTO sqlt_data_5_2022_11 (t_stamp, intvalue, floatvalue, stringvalue, datatype, tagid)
SELECT old.t_stamp, old.intvalue, old.floatvalue, old.stringvalue, old.datatype, 5262
FROM sqlt_data_5_2022_11 AS old
LEFT JOIN sqlt_data_5_2022_11 AS existing
ON existing.tagid = 5262 AND existing.t_stamp = old.t_stamp
WHERE old.tagid = 5261
AND existing.tagid IS NULL;

1 Like
8

Oh, I didn't realise this was still here. The simpler and significantly faster option is actually the reverse. Instead of updating the data tables, update the tagpaths in the tag table, modifying the old tag paths to the new tag paths. Then you're updating a single table of 1000's of records instead of 10's of tables of billions of records

9

yes haha… what if you wanna copy

10

If you want a copy then backup the table data :slight_smile:

11

And set retired to null, and restart the historian to rebuild its cache or you'll end up recreating a tag entry when the tag's value changes.
You may also want to delete the obsolete tag entry. Or not, your pick.

1 Like
12

Hey, the only issue I had when copying history from one tagid to a brand new tagid is that in the power chart the new tag would not show old manually inserted data. Even if inserted with the supported Ignition python API the old data would not appear on the PowerChart.

To fix this I edited the ‘created’ timestamp of the brand new tag. Making it appear as it it were created months ago. Now the Power Chart graphs the data appropriately.

Backing up the table data is not what I’m after, but rather copying a tag and moving its history with it. Or renaming a tag and moving the history of the last tagpath name onto in. (Although in that case it’s likely that changing the name in the sqlth_te table would work)

13

It “works”, with some quirks. I know first hand, as I had a similar requirement.
If you rename a tag in sqlth_te, it works just fine… but if the tag with the new name receives data, it will create a new tag entry in the historian, with the same path. That’s because manually changing the path doesn’t update the cache, so receiving a value with that path results in a cache miss and the historian creates a new one.
That’s why you need to restart your historian provider after changing the path, so it can rebuild its cache.
If you only have to do this once in a while, that’s not an issue.

2 Likes