I need to access historical data outside of the Ignition ecosystem. So, after some hacking (in the good sense of the word) I came up with the following script to return the closest (back in time) history record for a specific tag and date.
[code]declare @datetime datetime, @tag varchar(100)
set @datetime = ‘2017-03-04 15:58:30’
set @tag = ‘Line 3/Drives/MiddleRoll/ActualSpeedFPM’
– 1. Convert date into epoch date
declare @epochdatetime bigint – Date of data wanted
set @epochdatetime = DATEDIFF(s, ‘1970-01-01 00:00:00’, @datetime)
set @epochdatetime = @epochdatetime * 1000
– 2. Determine correct table
declare @partition varchar(100) – Partition table name
set @partition = (SELECT top 1 [pname] FROM [Ignition].[dbo].[sqlth_partitions] where start_time <= @epochdatetime order by start_time desc)
– 3. Determine correct id
declare @histagid bigint – ID of the correct tagid in the history tables
set @histagid = (select id FROM [Ignition].[dbo].[sqlth_te] where tagpath = @tag and created <= @epochdatetime and (retired > @epochdatetime or retired is null))
– 4. Select latest tag from correct partion
declare @SQL varchar(1000) – SQL to execute
set @SQL = ‘select top 1 intvalue, floatvalue, stringvalue, datevalue FROM [Ignition].[dbo].[’ + @partition + ‘] where tagid = ’ + cast(@histagid as varchar(100)) + ’ and t_stamp < ’ + cast(@epochdatetime as varchar(100)) + ’ order by t_stamp’
This returns one record with 4 columns. Three of these are null and the fourth has the data I need. I have created a stored procedure and it works. I think. Has anyone done it another way?
History Table Design Questions: A partition’s end date is identical to the next partition’s start date in the partition table. Which partition has midnight data? Does everything get logged at the start of a new partition?