# Avarage tag history between selected time to sql database **URL:** \ **Category:** Ignition\ **Created:** [June 27, 2023, 8:17am UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566 "2023-06-27T08:17:28Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\ **Post date:** [June 27, 2023, 8:17am UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/1 "2023-06-27T08:17:28Z")
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? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 27, 2023, 8:26am UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/2 "2023-06-27T08:26:57Z")
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](https://docs.inductiveautomation.com/display/DOC81/Aggregates) to retrieve the average value and write that to a summary table that never gets deleted. ---
**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\ **Post date:** [June 27, 2023, 8:36am UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/3 "2023-06-27T08:36:43Z")
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. ---
**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\ **Post date:** [June 27, 2023, 11:20am UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/4 "2023-06-27T11:20:51Z")
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? ---
**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:** [June 27, 2023, 1:18pm UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/5 "2023-06-27T13:18:43Z")
You should take @Transistor's advice. ---
**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\ **Post date:** [June 27, 2023, 1:41pm UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/6 "2023-06-27T13:41:57Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 27, 2023, 4:39pm UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/7 "2023-06-27T16:39:53Z")
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. ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [June 27, 2023, 9:26pm UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/8 "2023-06-27T21:26:10Z")
> [@Remo\_Aerts](#): > > will delete tag history from the database 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. > [@Remo\_Aerts](#): > > get the tabels that have values where the time stamp is between the 2 datetimes i selected 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. > [@Remo\_Aerts](#): > > l insert the average value of a variable (between 1 hour and 1 day). 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](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagCalculations) to get the average values you want to store. ---
**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\ **Post date:** [June 28, 2023, 6:42am UTC](https://forum.inductiveautomation.com/t/avarage-tag-history-between-selected-time-to-sql-database/75566/9 "2023-06-28T06:42:49Z")
okay. Unfortunately, I was just hoping that this was possible. But still, thank you.all"