I agree with diat150 about double logging data. It is already there, so why not access it.
Here is how I have been accessing the data to show what I want.
I leave the load on the SQL server as that is what it is good at.
First, I created a table with the following custom properties:
start → bind this to the date from my start popup calendar.
end → bind this to the date from my end popup calendar.
lowlimit → display tag values < this limit
highlimit → display tag values > this limit
tagpath → path to the historical tag.
update → I toggle this bit to refresh the query therefore the table.
id → Get the tag id from the sqlth_te database by binding the following SQL query
SELECT id
FROM sqlth_te
WHERE tagpath = '{Root Container.Table.tagpath}' AND retired is NULL
tables -->Based on the start / end properties, get all the table names where the data is stored by binding the following SQL query.
SELECT ISNULL(
/* Get the table names from the partions. */
(SELECT TOP 100 pname as pname
FROM sqlth_partitions
/* Convert the start property to unix time for comparison */
WHERE start_time/1000 > DATEDIFF(SECOND,'19700101','{Root Container.Table.start}')
/* Convert the end property to unix time and add a month to it for comparison */
AND end_time/1000 < DATEDIFF(SECOND,'19700101',DATEADD(MONTH,1,'{Root Container.Table.end}'))
ORDER BY pname),
/* Ok, you are in the current month, so get that table. */
(SELECT TOP 1 pname as pname
FROM sqlth_partitions
ORDER BY end_time DESC))
I partition my history on the month. If you do something different, change the “DATEADD(MONTH” to what you use.
query → This is the query I want to table to return. I get this from my history query script.
Query script:
def query(event):
id = event.source.getPropertyValue('id')
tables = event.source.getPropertyValue('tables')
limit = event.source.getPropertyValue('limit')
highlimit = event.source.getPropertyValue('highlimit')
lowlimit = event.source.getPropertyValue('lowlimit')
start = event.source.getPropertyValue('start')
end = event.source.getPropertyValue('end')
for row in range(tables.rowCount):
table = tables.getValueAt(row,0)
if row == 0:
query = "SELECT DATEADD(SECOND,((t_stamp/1000) + DATEDIFF(SECOND,SYSDATETIMEOFFSET(), GETDATE())), '19700101') as t_stamp, floatvalue as pv FROM " + table + " WHERE tagid = " + str(id)
query += " AND t_stamp/1000 > (DATEDIFF(SECOND,'19700101', '" + start + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
query += " AND t_stamp/1000 < (DATEDIFF(SECOND,'19700101', '" + end + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
if highlimit != '':
query += " AND floatvalue > " + highlimit
if lowlimit != '':
query += " AND floatvalue < " + lowlimit
else:
query = "UNION ALL SELECT DATEADD(SECOND,((t_stamp/1000) + DATEDIFF(SECOND,SYSDATETIMEOFFSET(), GETDATE())), '19700101') as t_stamp, floatvalue as pv FROM " + table + " WHERE tagid = " + str(id)
query += " AND t_stamp/1000 > (DATEDIFF(SECOND,'19700101', '" + start + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
query += " AND t_stamp/1000 < (DATEDIFF(SECOND,'19700101', '" + end + "') - DATEDIFF(SECOND,SYSDATETIMEOFFSET(),GETDATE()))"
if highlimit != '':
query += " AND floatvalue > " + highlimit
if lowlimit != '':
query += " AND floatvalue < " + lowlimit
query += " ORDER BY t_stamp DESC"
event.source.setPropertyValue('query', query)
Basically, it builds the sql query that I want to run. I only need ‘floatvalue’ for my needs.
So fairly easy and straightforward…
I exported a History Window so you can take a look at it.
Ignition V7.5
Cheers,
Chris
History Window.proj (14.8 KB)
