# Tag Historian SQL Queries **URL:** \ **Category:** Ignition\ **Created:** [June 26, 2018, 9:45am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621 "2018-06-26T09:45:50Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [June 26, 2018, 9:45am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/1 "2018-06-26T09:45:50Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 26, 2018, 11:28am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/2 "2018-06-26T11:28:37Z")
Don’t write SQL. Tag history is conveniently available in reports without SQL, and it takes care of the details, including spanning multiple tables. ---
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [June 28, 2018, 1:31am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/3 "2018-06-28T01:31:31Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 28, 2018, 1:38am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/4 "2018-06-28T01:38:44Z")
> [@neill](#): > > ideally I would like to do this in SQL 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. ---
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [June 28, 2018, 2:12am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/5 "2018-06-28T02:12:29Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 28, 2018, 11:28am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/6 "2018-06-28T11:28:26Z")
Transaction groups can use the store-and-forward subsystem, so they’ll catch up if you are pushing to a remote DB. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [June 28, 2018, 1:15pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/7 "2018-06-28T13:15:16Z")
> [@neill](#): > > 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 doc **Ignition Auto-Generated Tables:** [https://docs.inductiveautomation.com/display/DOC79/Ignition+Auto-Generated+Tables](https://docs.inductiveautomation.com/display/DOC79/Ignition+Auto-Generated+Tables) If you are using MySQL, try this. ```auto 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. ---
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [June 28, 2018, 8:15pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/8 "2018-06-28T20:15:42Z")
This is exactly what I was looking for. Thanks for the advice too. ---
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [June 28, 2018, 8:53pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/9 "2018-06-28T20:53:16Z")
That’s really useful to know,I have something to go on now. Thanks for the assistance. ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [June 28, 2018, 9:09pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/10 "2018-06-28T21:09:38Z")
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): ```auto 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: ```auto 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 ``` ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [June 29, 2018, 4:58am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/11 "2018-06-29T04:58:22Z")
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. > **[Dynamic - Angularjs Smart Table + Chart JS + JSON Integration. - Plunker](https://embed.plnkr.co/lu30E4WYjCSuFlayECxF/)** > > Created on Plnkr: Helping developers build the web. > [@Ignition-ReactJS-Django-JSON Realtime](http://forum.inductiveautomation.com/t/ignition-reactjs-django-json-realtime/18307): > > Demo here: [http://decisive-destruction.surge.sh/](http://decisive-destruction.surge.sh/) Git code here: Download. play, modify and share. The game begins NOW !!! ---
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [July 25, 2018, 4:36am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/12 "2018-07-25T04:36:45Z")
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](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. ---
**Author:** ![kumar.chellappan](https://avatars.discourse-cdn.com/v4/letter/k/2acd7d/32.png) [@kumar.chellappan](https://forum.inductiveautomation.com/u/kumar.chellappan)\ **Post date:** [August 19, 2020, 4:20pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/13 "2020-08-19T16:20:53Z")
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. ---
**Author:** ![neill](https://avatars.discourse-cdn.com/v4/letter/n/edb3f5/32.png) [@neill](https://forum.inductiveautomation.com/u/neill)\ **Post date:** [August 20, 2020, 3:40am UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/14 "2020-08-20T03:40:24Z")
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](https://www.inductiveuniversity.com/video/data-sources-scripting/8) ---
**Author:** ![kumar.chellappan](https://avatars.discourse-cdn.com/v4/letter/k/2acd7d/32.png) [@kumar.chellappan](https://forum.inductiveautomation.com/u/kumar.chellappan)\ **Post date:** [August 24, 2020, 10:29pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/15 "2020-08-24T22:29:37Z")
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 ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [July 8, 2021, 3:51pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/16 "2021-07-08T15:51:33Z")
Does anyone have the MS SQL syntax version of this? MySQL ```auto 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 ``` ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [July 8, 2021, 8:05pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/17 "2021-07-08T20:05:30Z")
I think replace ```auto FROM_UNIXTIME(a.t_stamp/1000) as time ``` with ```auto dateadd(S, a.t_stamp/1000, '1970-01-01') as time ``` ---
**Author:** ![cody.abbott](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cody.abbott/32/17533_2.png) [@cody.abbott](https://forum.inductiveautomation.com/u/cody.abbott)\ **Post date:** [July 8, 2021, 8:33pm UTC](https://forum.inductiveautomation.com/t/tag-historian-sql-queries/18621/18 "2021-07-08T20:33:25Z")
I use a scalar-valued function called dbo.UnixTStoDT. This converts unix timestamps to sql datetime format. example query: ```auto 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: ```auto 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 ```