I need to get the value of some historised tag.
As I know the moment (datetime), I’ve to get the name of the partition of history data inside the table sqlt_partitions. But the question is: how are the columns start_time and end_time represented? which format do they use? I saw those columns are bigint format, but if I need to filter in two datetimes, I have to convert to the correct format.
How can I know what dates includes in each partition?
For example: the table sqlt_partitions indicates that some partition of history tags is include between start_time and end_time. But, which are these dates?
The timestamps are stored in millisecond level unix-timestamp format (milliseconds since 1970, utc). This is for several reasons, but one is so that we can get MS level accuracy even with databases like MySQL, which don’t support milliseconds.
To convert to a date, you’ll have to use a function appropriate for your database. For mysql, for example, you could do:
SELECT from_unixtime(start_time/1000) FROM sqlth_partitions
The “/1000” is necessary because, like I said, MySQL doesn’t support milliseconds.
Now, I have another question in the same way to history tags.
I need to know how are called the tables of history data. If you configure data to be partitioned year by year, does the sqlt_data_… table follow any rule to get the name of it?
Deppending on the year, can you know which table you have to go in order to get the values from that year?