Data Partitioning and Pruning

Tag Historian will partition data into separate tables according to the time setting so that one table doesn't grow indefinitely, and then will delete old data to ensure the system is maintained for query performance. The data prune feature will delete partitions with data older than a specific age/time.

To partition and prune data

  1. Go to the Configure section of the Gateway.

  2. Select Tag > 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.

  3. Click on edit at the far right of the database you want make changes to.

    Main

    Data Connection

    Name of the Data Connection for the Tag History Provider.

    Enabled

    By default, the check box is selected (enabled) meaning the provider is turned on and accepts tag history data.

    Data Partitioning

    Enable Partitioning

    By default, partitioning is enabled to improve query performance. Tag Historian partitions and breaks up the data into separate tables based on time. Partitions will only be queried if the query time range includes their data, thereby avoiding partitions that aren't applicable and reducing database processing. On the other hand, the system must execute a query per partition. It is therefore best to avoid both very large partitions, and partitions that are too small and fragment the data too much. When choosing a partition size, it is also useful to examine the most common time span of queries.

    Partition Length and Units

    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.

    Enable Pre-processed Partitions

    This option is not selected by default. 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.

    Pre-processed Window Size (seconds)

    When pre-processing is turned on, the data will be summarized into blocks of this size

    Data Pruning

    Enable Data Pruning

    The check box is not selected/enabled by default. Partitions with data older than a specific age are deleted and if the data is not archived, the data is then lost.

    Important: Data pruning works by deleting old partitions. Therefore, data will only be removed when a partition has no data younger than the prune age.

    Prune Age and Units

    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.

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. This article will quickly cover how to do it in MySQL and MSSQL.

Both examples below assume the partion table is named 'sqlt_data_1_2016_08'.

MySQL
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 spit out the current timestamp.
Usage:

SELECT FROM_UNIXTIME(t_stamp/1000) FROM sqlt_data_1_2016_08

MSSQL
In Microsoft SQL Server, it's a little more verbose. We use the DATEADD() function to figure out the timestamp.
Usage:

SELECT DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') FROM sqlt_data_1_2016_08