Ignition report, SQL query help

Ignition
1

I am working on a proof of concept for using an Ignition report.

I have two conveyors, one 2M, one 5M. Each conveyor has a prox sensor that a robot picks from.

I have Ignition logging to a tall table every second the status of the sensors (via a gateway script).

What I would like to do is have an Ignition report summary showing machine activity based on the sensor data.

I have completed IU, so really it is just the SQL I need help with. I am using MariaDB.

This is what raw data looks like.

image

The datatype of the sensor column is tinyint, not sure if this is the most suitable or not.

The ultimate goal is a bar chart showing activity, broken down by hour.

Note that the cycle time of the conveyor indexing after robot has picked, is about 3 seconds, so for a pick the raw data in one column will look something like:

1
1
1
1
1
0
0
0
1
1
1
1
1

I can do the SQL basics for getting the raw data, and am aware of group by etc functions, but a steer in the right direction would be appreciated. Long term, it is perhaps better to integrate these mettrics into PLC counters, but for now I don't have access to that.

2

Your tables are going to grow at a very fast rate using this method. Why not utilize a transaction group or insert query to insert a pick event into your sql table instead?

As far as the queries you will need, there are tons of resources out on the web to reference.

Frank

3
  1. Don't want to miss an event.
  2. Storage is cheap.
SELECT site, ffr as "2m", COUNT(ffr) as "2m qty", por as "5m", COUNT(por) as "5m qty", dt as "dateTime" FROM phgt
WHERE dt >= '2023-12-18 08:00:00'
  AND dt <  '2023-12-21 20:00:00'
GROUP by ffr, por, hour(dt), day(dt)
ORDER by idphgt desc

The grouping I have shows the same quantity for 2m and 5m, which isn't right:

image
image757×199 55.7 KB

Also I think I need to build logic into it, to detect a transition from 0 to 1 as one event.

4

Well you're counting the number of rows in both cases, it's always going to be the same. Did you mean to use sum() instead ?

1 Like
5

Yep, my bad, burning too much midnight oil. Had got the count from another forum post which I thought was similar but clearly not similar enough.

6

You will miss events either way if comms are interrupted between Ignition and the plc. You are correct, best solution is to queue these events in the plc and then utilize a handshake from ignition to remove them from queue.

Frank

2 Likes