Changing the table creation time on Historical data tables

Hi All,

I changed the partitioning of the Historical tables from monthly to daily and I see that since I changed it around 10.20, Everyday the tables are created at the same time.

When the initial data base was created, the partitioning was 1 month and the tables were created at 00.00 hrs.

But since the change over its created at 10.20 each day.

I was wondering if there is a way to get the tables to be created at 12 o clock each night so that each table has the data stored from when the day starts?


I'm wondering this same thing

I received a response from IA that manages this. It involves modifying the sqlth_partitions table directly, & should be done with caution. IA explained to me that when a partition window passes, a new partition will only be created when there is a new data point, so the next day partition will not start exactly at 00.00 unless a tag is created 'exactly' then.

During the day, I modified the 'end_time' column in the sqlth_partitions table to midnight, & the partitions are roughly reset. After a few days of being set exactly at midnight, the partitions now start/end a couple seconds after midnight, which is fine for us.

I imagine you could have a script update the end_time column each morning to midnight if you needed it close to/exactly 00.00 every day:

UPDATE sqlth_partitions sp
SET end_time = 1701410400000
where sp.start_time = (SELECT MAX(start_time) FROM sqlth_partitions sp2 )