Display duration as 'hh:mm:ss' in chart y axis

Ignition
1

Hi,
I have written the below query to display duration vs loss in a vision chart,

SELECT l.Category as label, SEC_TO_TIME(COALESCE(SUM(m.RunTime), 0)) as value
FROM tbl_losscategory l 
LEFT JOIN tbl_machinestatus m ON m.ProcessArea	 = l.Process AND m.LossCategory = l.Category
WHERE 
 l.Process = 3 
GROUP BY label;

But i am getting the below error when i pass the above query as named query to a chart,

Traceback (most recent call last):
  File "<event:actionPerformed>", line 15, in <module>
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLException: The value '548:59:36' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.

	caused by GatewayException: java.sql.SQLException: The value '548:59:36' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.
	caused by Exception: java.sql.SQLException: The value '548:59:36' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.

Ignition v8.1.25 (b2023021409)
Java: Azul Systems, Inc. 11.0.17

It is working fine when i pass the query without converting it into SEC_TO_TIME format.
How to avoid this error and achieve a graph displaying as below,
image

2

You would need to retrieve the value as a number, then do the formatting locally in the chart to make our charting system happy.

1 Like