Hello
I am trying to make a script that will delete tag history from the database but will insert the average value of a variable (between 1 hour and 1 day).
The first problem i have is that i need to get the tabels that have values where the time stamp is between the 2 datetimes i selected.
does anyone have a good idee?
This sounds like a bad idea unless you really understand how the historian works. If you get it wrong you will wreck the database and lose your history.
Instead, consider using a short-term history provider that will auto-prune after a month or a week or whatever you like. Write a query on that and use the aggregate functions to retrieve the average value and write that to a summary table that never gets deleted.
I understand your concerns. Now, I will first put all the data into a CSV file before deleting the information. The intention is to make sure that it can also be restored.
The issue with putting the script on the tags is that it needs to be universal and usable for any project.
i also only will delete the sqlt_data_*_***** tabels don't thouch everyting else.
the script what i did try is to loop every tabel and select the values that are between the selected start- and enddate. but this takes a lot of time. any other way?
You should take @Transistor's advice.
do u think it will crach?
i olreaddy got a similar script only that one does not insert the avarage per hour. but just delete and set back.
The historian makes a tempting target for solutions like this, because it's just data in your database, right?
But it's really not - the storage algorithm is somewhat opaque, there's only sparse documentation for the table structure and flags, and it's in general a bad idea to try to manually manipulate historian tables.
If you want to have full control over how your data is stored, you should be using transaction groups or fully scripting your database logic. Not trying to abuse the historian into the shape you want.
This is what the database's prune setting is for. It automatically deletes data that is beyond the age specified. The default setting is one year, but you can change this to pretty much whatever you want up to your hardware limitations. If the prune setting is one year, your data will automatically fall off after that length of time, and there is no need for a special script.
This will depend on your database's partition settings. The default partition setting is one month, which means that every month, a new table will be created. The idea behind this approach is to limit the size of the tables to make the querying run faster. This setting can be changed to anything from one millisecond [which would be absurd] all the way up to your hardware limitations. I believe the table names correspond to their creation date, but for what you are wanting to do, nobody with experience would recommend this approach.
You're wanting to preserve the average values for longer than your database's pruning setting in way that doesn't use a large amount of space. That's fine, but do it in a regular database with your own table structure. Use the innate prune setting to delete your old data, and use the built in system.tag.queryTagCalculations to get the average values you want to store.
okay. Unfortunately, I was just hoping that this was possible. But still, thank you.all"