Hello everyone, I'm trying to find the format for converting from epoch time to normal time. We have a PLC logging timestamps in epoch time and I can't get it working.
I need to pull the last 3 minutes of data from this table and average the weight for our products but can't find a format thats working when trying to setup a named query. Can anyone point me to some documentation that should work for this?
Show us a sample of your data and your SQL query.
Format them properly with the </> button. See Wiki - how to post code on this forum if unsure how.
Here is my query I was looking at based on another post and some information i found online for converting epoch time.
SELECT
AVG(floatvalue) as avg_floatvalue,
to_timestamp(t_stamp) as converted_t_stamp
FROM sqlt_data_3_2024_04
WHERE tagid = 161 AND to_timestamp(t_stamp) >= DATEADD(MINUTE, -3, GETDATE())
Here is a screen show of the table im pulling from. I need to average the float.
For MSSQL:
DATEADD(s, t_stamp/1000, '1970-01-01')
ill try it out
Why are you manually querying the Tag History tables, rather than using the built in functions?
Your query will fail (silently) if your 3 min sample crosses partition boundries.
You are running direct queries against Ignition's historian tables. This is extremely unwise (and unmaintainable). Use Ignition native historian access methods.
Matrix I think you saved me a headache, its not throwing an error, I just need to check to verify the math is working properly!
oh, I will look into it, I didn't realize it was not a good idea.
Or for a binding, use a Tag History Binding
I'm not familiar with that as I'm learning Ignition on the fly for my work. Is there some documentation that you are aware of you can point me to to provide some depth on it? If not I appreciate the heads up!
I took over this project but before me, the company I work for has a PLC writing directly to the Table I'm trying to setup a query Tag for. Is this Bad?
Sounds like I should be having the data flow through to ignition into a tag and enabling the history for it and using the system.tag.queryTagHistory feature?
It appears to me, based on the query that you showed, that by "PLC writing directly to the Table" what you're actually seeing is the result of an already existing OPC tag with History enabled.
I avoid query tags at all costs, they have there uses, but they are not a great solution for many things that they can be used for.
From the information you have provided, the correct way to collect the data you are interested in, is to use the pre-built in Tag History functions, weather that be with a binding or a script.
What is the real problem you are trying to solve? What are planning to do with this 3 minute sample of data once you get it back? Where are you trying to get this data from and to?
We have a simple dashboard that displays averages for certain things. In this case getting the average weight factors into calculating the how much giveaway we have in our product.
And this average needs to be displayed in what? A label? How is the time span selected is it always constant or does the user select?
I'll assume a Label, look into a Tag History Binding.
There isn't any user interaction really. Simply informational pretty much. If something is off or looks way wrong its a visual que to stop and adjust or inspect whats happening.
Its going to be displayed on a Label. It will be a constant value displayed when that production line is being run, which isnt always the case.
The TagHistory Binding requires a tag to be used right? I dont have a tag to use with that feature. All I have is a table to pull from.
You will want a tag history binding that uses return size = 1, for a time span from now() minus three minute to now(). And "average" aggregation mode. You'll get one row in the result, from which an expression binding can extract the one value.
The table you showed is part of the tag historian.
It seems unlikely that you don't have a corresponding tag.