Version: 8.1.24
In PowerChart I add some annotations in chart.
When I export to CSV, I want to export annotations together.
How to resolve it?
Don't write a manual query against historian tables.
Use system.tag.queryTagHistory and system.tag.queryAnnotations:
Then join your datasets with scripting, format to CSV, then use system.perspective.download to prompt the user to download the file.
There's no functionality built in to the power chart to do this, probably because of the 'line up the timestamps' problem Transistor alluded to.
2 Likes
That's a much better idea. I had seen those in the docs but never had occasion to use and forgot about them. I've deleted my answer lest I lead someone astray.
1 Like
I already query the result in Laboratory Database .
paths = [u"[Laboratory]TestTag222"]
startTime=system.date.addDays(system.date.now(), -30)
endTime = system.date.now()
types = ['note']
annotations=system.tag.queryAnnotations(paths, startTime, endTime,types)
for item in annotations:
print item
print item.data
If I want to query the result in other Database ,How to resolve?
You are running two queries which are returning datasets with different numbers of columns and different types.
You will need to write a script to combine the datasets. The "output" dataset will have to have all the columns of both tables but you will have to decide how to merge the historian t_stamp column and, presumably, the start_time column of the annotations table.
Then you will loop through the first dataset and copy the columns into the appropriate output columns and do the same with the second dataset. You would then have to sort by t_stamp or do that in Excel.
Maybe I have to use sql ,not use system.tag.queryAnnotations.
SELECT a.tagid , a.start_time ,a.end_time ,a.type, a.datavalue , t.id, t.tagpath FROM sqlth_annotations as a
left join sqlth_te as t on a.tagid=t.id
That doesn't give you the history data. You only get the annotations.
How to union many months data from different table（sqlt_data_1_2024_06，sqlt_data_1_2024_05） by the startTime and endTime?
Both Paul and I have advised you not to do that. Use Ignition's functions and script the combination of the data.
But system.tag.queryAnnotations can't Specify the database
Neither can system.tag.queryTagHistory | Ignition User Manual.
I would expect that both find the database connection from the tag history definition. See Tag Properties | Ignition User Manual.
1 Like
Project set Database ----Laboratory
But I add tag History set Database --- postgres
annotations=system.tag.queryAnnotations(paths, startTime, endTime,types)
Only query the tag's history Annotation of Laboratory, can't query the tag's history Annotation of postgres?
Tags are outside any project. They don't use your project's database setting. You've told the tag to use the postgres storage provider. That's where the annotations must go, too.
Ignition's API functions will use the correct database based on the tag settings.
1 Like
Tips:
- "postgres" is not a good name for a connection. It is the name of the database engine. Your Connect URL probably looks something like this:
jdbc:postgresql://host:port/YourDatabaseName
and you should set the connection name toYourDatabaseNameto make it obvious which database the connection connects to. - Then modify the Storage Provider in all the tags that use it. (You can multi-select and edit them all at once.)
You may be able to avoid losing history like this:
3. Create a new connection to jdbc:postgresql://host:port/YourDatabaseName (the same database as before) and give it a sensible name.
4. Then convert your tags to use the new connection. All the data will go to the same database.
5. Disable the original postgres database connection and confirm that history and annotations are still being recorded.
6. If successful then delete the postgres connection.
That will make it much clearer to whoever has to maintain the system - even if it is you.
1 Like