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
Is there a reason to not use a status chart? It would save a boatload of work for you.
2 Likes
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).