Historical machine status on pie chart

Ignition
1

I need to make a pie chart with the historical status of the machine.
the different status are:
0: Stop
1: Run
2: Slow Run

It is important that I can specify the time between which the status should be displayed.
can anybody help me with that?

1 Like
2

Is there a reason to not use a status chart? It would save a boatload of work for you.

2 Likes
3

Check this thread out.

1 Like
4

I agree with @JordanCClark. I remember trying to implement my own status tracking using the historian, but it was painful. I eventually decided to store everything myself in a custom schema, with columns for status, start datetime, and end datetime. The current status of any machine is stored in a row with endtime = Null.

When the status tag of a machine changes, a named query is called from the OnValueChanged tag event script. That query is a transaction that closes the previous status by writing its endtime = current time and starting a new status row with starttime = now.

If you think this might be better approach for what you’re trying to do, here are the named queries (with MsSQL syntax):

MachineStatus/LogMachineStatusChange (params: equipment_id - String, status - Int4):

SET xact_abort on;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
DECLARE @now datetime = GETDATE()
UPDATE [dbo].[machine_status_log]
	SET
		[end_timestamp] = @now
	WHERE
		[end_timestamp] IS Null
		AND [equipment_id] = :equipment_id;
INSERT INTO [dbo].[machine_status_log] (equipment_id, start_timestamp, end_timestamp, status)
	VALUES (:equipment_id, @now, Null, :status);
COMMIT TRANSACTION;

MachineStatus/GetMachineStatusDuration (params: equipment_id - String, status - Int4, start - datetime, end - datetime):

SELECT
	SUM(datediff(
        second, 
        CASE WHEN start_timestamp < :start THEN :start else start_timestamp END,
        CASE WHEN end_timestamp IS NULL THEN :end
             WHEN end_timestamp > :end THEN :end else end_timestamp END
        )
    ) AS duration
FROM machine_status_log
WHERE (:start <= start_timestamp OR :start BETWEEN start_timestamp AND isnull(end_timestamp, :end))
	AND (:end >= end_timestamp OR :end BETWEEN start_timestamp AND isnull(end_timestamp, :end))
	AND equipment_id = :equipment_id
	AND status = :status
GROUP BY [status]

The case conditions in the SELECT clause and the isnull() conditions in the WHERE clause handle the truncation of statuses that start/end outside of your requested timeframe. For example, if you have a status that runs from 3:25-4:25, but you request all statuses from 3:30-4:00, it will give you that status code with a duration of 30 minutes (in seconds).