Hello,
in a report, I would like to query the value of a tag/parameter "temperature" at a certain time "timestamp".
What would be the easiest way to do this? From what I understand, I could use scripting, SQL query or an expression, but since I am inexperienced with all 3 I can't find a solution on my own.
SQL seemed the most intuitive, so I got as far as:
SELECT column
FROM table
WHERE t_stamp = timestamp
but this raises the questions:
In which table are the values saved?
In which column is the tagname saved?
How can I enter the timestamp that is saved in Ignition in this format, so that SQL can read it? dateFormat({[default]temperature},"dd.MM.yyyy HH:mm:ss")
Essentially, you need to query the historian for the data, and you can allow the user to select the time they are interested in.
You can also do this through a Script Data Source if the Tag Historian Data Source doesn't meet your exact needs. You can read about that at these links:
Technically, you can query the Tag History Tables directly, but that is not recommended because they are generally partitioned and using the built in functions takes care of the gory details of crossing partition boundaries for you.
Hello,
I have tried the tag historian query yesterday but I got the error "ERROR: class java.lang.String cannot be cast to class java.util.Date (java.lang.String and java.util.Date are in module java.base of loader 'bootstrap')". I found out that this was because I formatted my start and end time with dateFormat().
I am rather new to ignition but I have been enjoying using the named Queries.
Below is an example of a query, that uses the Parameter to put in the time value to go backwards and query from. I called it Minutes back. You can also allow for 2 inputs of time ranges, and use a BETWEEN. I also like to convert the time from T_stamp because I like to be able to read it.
Now the parameter for the named query just needs linked to a value in your project!
One thing to note with named queries, is I have the database set to not create new tables every month. If you use named queries when the database creates a new table for the new month, or week or whatever... default setting is month, it breaks the named queries. I then wrote some of my Queries to index the data how I want and keep my database clean.
SELECT
Dateandtime,
ROUND([15]/100,2) AS 'T4A1 PH'
FROM
(
Select
CAST(DATEADD(SECOND, (t_stamp/1000) - 18000,'1970/1/1') AS DateTime) DateandTime,
tagid, floatvalue
From Ignition_Server.dbo.sqlth_1_data
) as Source
Pivot
(
AVG(floatvalue) FOR tagid IN ([15])
)as PVt
WHERE dateandtime >= DATEADD(minute, :MinutesBack ,GETDATE())
AND [15] is not null
AND [Dateandtime] is not null
Order by DateandTime asc;
Whether you enjoy them or not, they are a terrible choice for retrieving from the historian's automatic tables. It is very difficult to reliably get the right _data table names, nor to properly UNION partitions together based on timestamps, nor to properly convert long timestamps to Java datetime values in a DB-brand agnostic way.
The historian's native queries can deliver the last value in a time frame simply by providing that timeframe, choosing result size == 1, and choosing last value as the aggregation mode.
Please do not recommend direct queries of historian data here unless you are prepared to help the OP in navigating all of the nuances that apply to their situation.
So if you used this method, you would alter this code below from:
WHERE dateandtime >= DATEADD(minute, :MinutesBack ,GETDATE())
to
Where dateandtime = MinutesBack
but presumably you could use a variable named 'TimeSelection' or something of that nature, and rather than storing numbers like -10 as I did, you would use the DateTime variable in the parameter group. Then, you can use the Date and time picker perspective component.
