Partition and Prune Data
- Go to the Config tab of the Gateway.
- Select Tags > History from the menu on the left.
The Historical Tag Providers page is displayed. You can see the Databases that have Enabled tag history on and their Status shows as Running.
- Click on edit at the far right of the provider you want to update.
- Once you've made changes, click Save Changes at the bottom of the screen.
The following table describes all the settings available for Tag History:
|Provider Name||Name of the Tag History Provider, for example, MySQL.|
By default, the check box is selected (enabled) meaning the provider is turned on and accepts tag history data.
|Description||Description of the Tag History Provider (optional).|
|Enable Partitioning||The built-in partitioning system breaks up data into separate tables of a specified time frame. This can improve performance and make certain maintenance tasks easier. Default is true.|
The size of each partition, the default is one table per month. Many systems whose primary goal is to show only recent data might use smaller values, such as a week, or even a day. Default is 1.
|Partition Units||Unit of time for the partition length. Options are: Milliseconds, Seconds, Minutes, Hours, Days, Weeks, Months, and Years. Default is Months.|
|Enable Pre-processed Partitions||Pre-processed partitions will use more space in the database, but can improve query speed by summarizing data, reducing the amount that must be loaded. Default is false.|
|Pre-processed Window Size (seconds)||When pre-processing is turned on, the data will be summarized into blocks of this size. Default is 60.|
Enable Data Pruning
Partitions with data older than a specific age are deleted and if the data is not archived, the data is then lost. Default is false.
The maximum age of data. As mentioned, the data is deleted by the partition, and could therefore surpass this threshold by quite a bit before all of the data in the partition is old enough to be dropped. Default is 1.
|Prune Age Units||Unit of time for the prune age. Options are: Milliseconds, Seconds, Minutes, Hours, Days, Weeks, Months, and Years. Default is Years.|
|Show Advanced Properties||Select this option to display the Advanced properties below:|
|Enable Stale Data Detection|
If enabled, tracks tag group executions to determine the difference between unchanging values, and values that are flat due to the system not running. Default is true.
|Stale Detection Multiplier|
The multiplier for tag group rate used to determine when values are stale. If tag group execution is not recorded within this amount of time, values will be considered bad on query. Default is 2.
History Table Timestamps
If you've looked behind the scenes of SQLTags Historian, you've probably noticed the timestamps are not stored as standard SQL timestamps. They are stored in a variant of Unix time, or the number of milliseconds since January 1, 1970 00:00:00. The time may come when you need to convert that timestamp to a more human-readable format. The following describes how to do it in MySQL and MSSQL.
Both examples below assume the partition table is named 'sqlt_data_1_2016_08'.
It's pretty easy to deal with Unix timestamp in MySQL because they have a built-in function for doing so. The FROM_UNIXTIME() function will take in a Unix timestamp and return the current timestamp.
In Microsoft SQL Server, it's a little more verbose. We use the DATEADD() function to figure out the timestamp.