Tag Historian SQL Queries

Ignition
1

Hello,

I am currently involved in a project where we have a remote edge gateway connected to a local enterprise gateway.
Our client is wanting a variety of reports based on the data we are logging on the edge gateway.
Just wondering if I could get a little overview on how write sql queries, more specifically on how to find the columns that a specific tag is stored. It would be great if this information was added to the Ignition docs.

Thanks in advance.

2

Don’t write SQL. Tag history is conveniently available in reports without SQL, and it takes care of the details, including spanning multiple tables.

1 Like
3

Thanks for the reply.
Tag History is great for basic reporting, however I am attempting to find the difference between two values to show a change over a specific time period (e.g accumulated flow over each hour between two dates).
I have implemented some expression tags to do this arithmetic but ideally I would like to do this in SQL.

4

Consider using Transaction Groups to record to conventional "wide" tables. I prefer them over tag history for many use cases. And you can fully apply your DB's analytics.

1 Like
5

I am unsure of how I could use transaction groups for this. More specifically in the instance where tag history is syncing after the edge gateway has lost its connection for several days. I am a fairly new to SQL and cannot see any easy way to automate this.

6

Transaction groups can use the store-and-forward subsystem, so they’ll catch up if you are pushing to a remote DB.

1 Like
7

Ignition Auto-Generated Tables:
https://docs.inductiveautomation.com/display/DOC79/Ignition+Auto-Generated+Tables

If you are using MySQL, try this.

SELECT a.tagid, b.tagpath, a.intvalue, a.floatvalue, a.stringvalue, 
FROM_UNIXTIME(a.t_stamp/1000) as time 
FROM demo.sqlt_data_1_2018_06 a, demo.sqlth_te b
where a.tagid = b.id
order by tagid asc, t_stamp desc

My best suggestion to learn sql is, play with the DB query tool (workbench in case of mysql). Whatever works in workbench or any other query tool, will work in Ignition. Just do some google search, you will find a lot of help and sql snippets.

5 Likes
8

This is exactly what I was looking for. Thanks for the advice too.

9

That’s really useful to know,I have something to go on now. Thanks for the assistance.

10

Are these values different tags? Or the same tag, but just at the start of the hour and the end of the hour?

If it’s the same tag, and it’s steadily increasing, you should be able to do what you want with the queryTagHistory function.

Edit: Here’s an example. The tag I’m using is a totalizer; the current total is in the 10,xxx range right now. This query should be showing the total used in each hour. I’m making no effort to align the start date and end date to actual hour boundries, but that would be easy enough to do.

Here’s the code (I’m doing this in the script console, BTW):

endTime = system.date.now()
startTime = system.date.addHours(endTime, -30)
data = system.tag.queryTagHistory(paths=['VerisMainPower/Energy Consumption'],startDate=startTime, endDate=endTime,aggregationMode="Range",intervalHours=1)
for row in range(data.rowCount):
   print data.getValueAt(row, 0),data.getValueAt(row, 1)

And here’s the output from the script:

Wed Jun 27 10:18:30 CDT 2018 76.20703125
Wed Jun 27 11:18:30 CDT 2018 57.859375
Wed Jun 27 12:18:30 CDT 2018 38.2734375
Wed Jun 27 13:18:30 CDT 2018 39.513671875
Wed Jun 27 14:18:30 CDT 2018 45.62890625
Wed Jun 27 15:18:30 CDT 2018 43.599609375
Wed Jun 27 16:18:30 CDT 2018 35.19140625
Wed Jun 27 17:18:30 CDT 2018 43.109375
Wed Jun 27 18:18:30 CDT 2018 37.74609375
Wed Jun 27 19:18:30 CDT 2018 44.771484375
Wed Jun 27 20:18:30 CDT 2018 34.564453125
Wed Jun 27 21:18:30 CDT 2018 40.0732421875
Wed Jun 27 22:18:30 CDT 2018 56.62890625
Wed Jun 27 23:18:30 CDT 2018 97.6298828125
Thu Jun 28 00:18:30 CDT 2018 69.12109375
Thu Jun 28 01:18:30 CDT 2018 45.638671875
Thu Jun 28 02:18:30 CDT 2018 39.73828125
Thu Jun 28 03:18:30 CDT 2018 87.501953125
Thu Jun 28 04:18:30 CDT 2018 60.1474609375
Thu Jun 28 05:18:30 CDT 2018 55.779296875
Thu Jun 28 06:18:30 CDT 2018 64.37890625
Thu Jun 28 07:18:30 CDT 2018 49.361328125
Thu Jun 28 08:18:30 CDT 2018 61.3876953125
Thu Jun 28 09:18:30 CDT 2018 75.734375
Thu Jun 28 10:18:30 CDT 2018 71.279296875
Thu Jun 28 11:18:30 CDT 2018 56.2626953125
Thu Jun 28 12:18:30 CDT 2018 65.9541015625
Thu Jun 28 13:18:30 CDT 2018 64.072265625
Thu Jun 28 14:18:30 CDT 2018 38.201171875
Thu Jun 28 15:18:30 CDT 2018 50.412109375
Thu Jun 28 16:18:30 CDT 2018 0.0
1 Like
11

This may be useful for those who want to build some Data visualization and reporting server. Many of my clients like this and some of them have built amazing DV&R apps with this boiler plate code.

12

Just an update for everyone.
I managed to design the reports I wanted by using a combination of tag historian queries and scripts as described in this page of the documentation: https://docs.inductiveautomation.com/display/DOC79/Scripting+Data+Source
I found it was super helpful for adding more functionality to historical tags in reporting.

Thanks for all the help.

1 Like
13

Hi,
I am trying to do the same here but not able to achieve it.
Could you please point me in the direction?
I thought of having tag history query and sub query and subtract previous values.
or is there a better way for it?

Thank you.

14

Hey,

I assume you are using the report module?
I found the best way to work with historical tags is to query the database with a tag historian query, then perform the more complex manipulation on the dataset returned.
This video illustrates how to do this:
https://www.inductiveuniversity.com/video/data-sources-scripting/8

1 Like
15

Hi, Thank you very much for the link. I was able to create it with the help of that video, documentation and support.

Regards,
Kumar

16

Does anyone have the MS SQL syntax version of this?

MySQL

SELECT a.tagid, b.tagpath, a.intvalue, a.floatvalue, a.stringvalue, 
FROM_UNIXTIME(a.t_stamp/1000) as time 
FROM demo.sqlt_data_1_2018_06 a, demo.sqlth_te b
where a.tagid = b.id
order by tagid asc, t_stamp desc
17

I think replace

FROM_UNIXTIME(a.t_stamp/1000) as time

with

dateadd(S, a.t_stamp/1000, '1970-01-01') as time
1 Like
18

I use a scalar-valued function called dbo.UnixTStoDT. This converts unix timestamps to sql datetime format.

example query:

SELECT TOP (1000) [tagid]
      ,[intvalue]
      ,[floatvalue]
      ,[stringvalue]
      ,[datevalue]
      ,[dataintegrity]
      ,dbo.UnixTStoDT(t_stamp)
  FROM [mydatabase].[dbo].[sqlt_data_1_2021_07]
  JOIN sqlth_te ON tagid = sqlth_te.id
  where sqlth_te.tagpath = 'mytag'

Here is the scalar function:

USE [mydatabase]
GO

/****** Object:  UserDefinedFunction [dbo].[UnixTStoDT]    Script Date: 7/8/2021 3:30:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[UnixTStoDT] (@BIGINT BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @TIMEDATE DATETIME
	DECLARE @TZ INT
	SET @TZ = -6
	SET @TIMEDATE= DATEADD(S, CONVERT(int,LEFT(@BIGINT, 10)), '1970-01-01')
	SET @TIMEDATE = DATEADD(HH, @TZ, @TIMEDATE)
	IF DATEPART(WK, @TIMEDATE) > 10 AND DATEPART(WK, @TIMEDATE) < 45
	SET @TIMEDATE = DATEADD(HH, 1, @TIMEDATE)

	RETURN (SELECT @TIMEDATE)




	
END;
GO