[size=150]Hello, I’m creating a Daily Report showing the average values of HC and CO for every hour of the day.
[/size]
Now, I wanted to know how do I get the total time of an active tag for every hour?
I tried to create a Tag Historian query in the Reports Tab > I Choose the Tag > Put the Data Range to Historical with custom Date Bindings (12 AM to 12 PM) > Aggregation Mode to “Duration On” > For every 60 minutes.
Result: The value is always Zero…
Then, instead of creating a Tag Historian, I did the same choosing the Tag Calculation query. But the problem is that I can’t choose the Sample Size to Interval > Every 60 min.
Result: I have the total value of the day(I think), in seconds, but I don’t think that it’s accurate, because it swings a lot.
Can someone, who had the same issue, help me please? I appreciate it!
Because I’m lazy, I’m going to assume that you’re using MySQL and that you can make a table that has the numbers 0-23 in it.
Your top level query will be something like…
SELECT num, DATE_SUB(NOW() ,INTERVAL num HOUR) AS interval_start, DATE_SUB(NOW() ,INTERVAL num + 1 HOUR) AS interval_end FROM numbers
Then you can nest your tag hist calc query, using interval_start and interval_end as your start and end times.
You’ll need to do a bit more massaging with the top level query to get the intervals to start and end at the top of the hour. Probably using CURDATE() instead of NOW() will get you where you want to be.
Thank You for your help, it really gave me another perspective about this issue.
So I did what you told me to, I created a table with a range of numbers from 0 to 23 in MySQL.[/size]
[size=150]After that, I went to my report in Ignition and created a SQL query to paste the code you sent me.[/size]
[size=150]I think it worked out because I could see a timestamp from the interval_Start to interval_end. [/size]
[size=150]But now I’m a bit stuck with the process, what did you wanted to say with “Then you can nest your tag hist calc query, using interval_start and interval_end as your start and end times.”? I couldn’t understand how do I add the interval_start and end as a data range for the Query.
Do I need to rename my current Date parameters to “interval_start” and “interval_end” then, nest it in the SQL Query with a Historical Data Range?[/size]
