Pre-Creating Historical Partitions

If a historical partition exists before Ignition creates it, will that cause any problems with Ignition? For example, if I want to create a partition for January 2012 would Ignition start using that partition when Jan came around or would it make it’s own partition?

I am making reports that directly get data from the monthly partitions and do some aggregation. Some of the reports require looking at a years worth of partitions at a time and if that partition doesn’t exist the SQL Query throws an error.

You can pre-create them. When the system goes to store data, it queries the sqlth_partition table for a table that covers the required time. If found, it verifies that the table is “valid”, meaning that it covers the range expected. However, if you’re using monthly partitions, it actually just looks at the table name. So, if you make sure to name the tables correctly, and make sure to insert the correct values into sqlth_partitions, you should be fine. In regards to inserting records, you can use something like: insert into sqlth_partitions(pname,drvid,start_time,end_time) values('sqlt_data_1_2011_12',1, unix_timestamp('2011-12-01 00:00:00')*1000,unix_timestamp('2011-12-31 23:59:59')*1000)

Regards,

There is a question, I have set the Data Partitioning 2 Months, and the table sqlth_partitions's record is pname: sqlt_data_1_20230308
start_time: 2023-03-08 21:08:38.000
end_time: 2023-05-07 21:08:38.000

And reset the Data Partitioning to 2 Days, but after 2 days, there is no changes in table sqlth_partitions, is that a bug?

Known and not great behavior, but not officially a bug. Your new partitioning will start on May 7th, IIRC.

1 Like