# Hacking History

**URL:** <https://forum.inductiveautomation.com/t/hacking-history/11316>\
**Category:** Archived Topics\
**Created:** [March 4, 2016, 4:25pm UTC](https://forum.inductiveautomation.com/t/hacking-history/11316 "2016-03-04T16:25:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![CJVR](https://avatars.discourse-cdn.com/v4/letter/c/0ea827/32.png) [@CJVR](https://forum.inductiveautomation.com/u/CJVR)\
**Post date:** [March 4, 2016, 4:25pm UTC](https://forum.inductiveautomation.com/t/hacking-history/11316/1 "2016-03-04T16:25:42Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![Sergey.Danilov](https://avatars.discourse-cdn.com/v4/letter/s/7cd45c/32.png) [@Sergey.Danilov](https://forum.inductiveautomation.com/u/Sergey.Danilov)\
**Post date:** [March 23, 2016, 12:40pm UTC](https://forum.inductiveautomation.com/t/hacking-history/11316/2 "2016-03-23T12:40:55Z")

</div>

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;`