Hacking History

Archived Topics
1

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’
execute (@SQL)[/code]

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?

Thanks,

2

I use the function

CREATE OR REPLACE FUNCTION public.get_hist_data ( start_time_f bigint, end_time_f bigint, tagpath_f text ) RETURNS TABLE ( data double precision, "time" bigint ) AS $body$ DECLARE cur1 CURSOR FOR SELECT pname FROM sqlth_partitions ORDER BY pname; tbl text; str_sql text; BEGIN open cur1; FETCH cur1 INTO tbl; WHILE FOUND LOOP str_sql = 'select floatvalue, t_stamp from ' || tbl ||' d, sqlth_te t where d.tagid=t.id and t.tagpath=''' || tagpath_f|| ''' and d.t_stamp >='||start_time_f||' and d.t_stamp <='||end_time_f||' ORDER BY d.t_stamp'; RETURN QUERY EXECUTE str_sql; FETCH cur1 INTO tbl; EXIT WHEN tbl IS NULL; END LOOP; close cur1; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER COST 100 ROWS 1000;