Chart data on "Y"

Ignition
, , ,
1

Hello Everyone

I have the next situation, I do a chart whit the next query

SELECT CONVERT(INT, CONVERT(VARCHAR,CONVERT(DATE,dm.Fecha),112)) AS 'Fechax',
SUM(CASE WHEN dm.Linea = 'ELFO_006_Oficinas' THEN dm.Dato - ISNULL( dp.Dato, 0) ELSE 0 END) AS ELFO_006_Oficinas_Diferencia,
SUM(CASE WHEN dm.Linea = 'ELFO_007_Rim'      THEN dm.Dato - ISNULL( dp.Dato, 0) ELSE 0 END) AS ELFO_007_Rim_Diferencia,
SUM(CASE WHEN dm.Linea = 'ELFO_008_ED'       THEN dm.Dato - ISNULL( dp.Dato, 0) ELSE 0 END) AS ELFO_008_ED_Diferencia,
SUM(CASE WHEN dm.Linea = 'ELFO_009_FBW'      THEN dm.Dato - ISNULL( dp.Dato, 0) ELSE 0 END) AS ELFO_009_FBW_Diferencia,
SUM(CASE WHEN dm.Linea = 'ELFO_010_Disco'    THEN dm.Dato - ISNULL( dp.Dato, 0) ELSE 0 END) AS ELFO_010_Disco_Diferencia
FROM Dato_Medidor dm
LEFT JOIN Dato_Medidor dp ON DATEADD( DAY, -1, CONVERT(DATE,dm.Fecha)) = CONVERT(DATE,dp.Fecha)
                         AND dm.Linea = dp.Linea
                         AND CONVERT(DATE,dp.Fecha) BETWEEN DATEADD( DAY, -1, CONVERT(DATE,'{Root Container.Popup Calendar.date}')) AND DATEADD( DAY, -1, CONVERT(DATE,'{Root Container.Popup Calendar 1.date}')) AND DATEPART(HOUR, dp.Fecha) = 7 AND DATEPART(MINUTE, dp.Fecha) = 0
WHERE CONVERT(DATE,dm.Fecha) BETWEEN CONVERT(DATE,'{Root Container.Popup Calendar.date}') AND CONVERT(DATE,'{Root Container.Popup Calendar 1.date}') AND DATEPART(HOUR, dm.Fecha) = 7 AND DATEPART(MINUTE, dm.Fecha) = 0
GROUP BY CONVERT(INT, CONVERT(VARCHAR,CONVERT(DATE,dm.Fecha),112)) 
ORDER BY CONVERT(INT, CONVERT(VARCHAR,CONVERT(DATE,dm.Fecha),112));

and this is my result

image
image1238×133 5.28 KB

image
image815×263 14.5 KB

the situation is the date of "y" change by hour and I need see on Date

2

Not exactly sure I'm following what you're asking, so:

Are you wanting to show the date on the Y-Axis instead of the X-Axis, or are you wanting to show only the date on a per day basis on the X-Axis?

Also, you're doing some odd conversions with your dates, what is the actual type of dm.Fecha in the DB?

3

I think like @lrose mentioned, your date is only returning a date, but needs to return a full date/time stamp in order to get the chart to work. Essentially you need to return a timestamp of midnight on each date you want on the chart and I think that will fix it.

1 Like
4

HI

ok, Let me explain a little more, I need the date the Colum "Fecha" on the "X" axis but right now it shows it as time and not like the int

5

The chart needs a timestamp. In this case, it's reading the date column as milliseconds since midnight on January 1, 1970, which with time zone -6, comes out to January 1, 1970 11:37:11 PM with various milliseconds (120, 121, and 122 in your screenshot). You can check this yourself by going here: https://www.epochconverter.com

Then since that uses seconds, enter just 20231 (and leave off the 120, 121, 122, etc) and you'll get the timestamp just like you have in your current chart.

So you need to convert your integer to a valid timestamp. Looking at your SQL though, it seems as though you're converting from an already existing date/timestamp to a string, and then to an integer in order to group by this summarized date/day. I'd have to brush up on my SQL skills, but it may need a nested SQL statement to reformat that column after it's summarized.

As a test, you can confirm you get the correct date when doing this in a named query or any SQL tool you're using by basically converting your integer to a string, and then back to a datetime:

SELECT STR_TO_DATE(CONVERT(20231120, CHAR),'%Y%m%d') AS Fetchax;
1 Like
6

I change by date but I have other error

java.lang.IllegalArgumentException: Illegal type for X value: class java.lang.String (column 'Fechax')
	at com.inductiveautomation.factorypmi.application.components.chart.runtime.XYNumericDataSet.getXValue(XYNumericDataSet.java:116)
	at org.jfree.data.general.DatasetUtilities.iterateDomainBounds(DatasetUtilities.java:699)
	at org.jfree.data.general.DatasetUtilities.findDomainBounds(DatasetUtilities.java:642)
	at org.jfree.chart.renderer.xy.AbstractXYItemRenderer.findDomainBounds(AbstractXYItemRenderer.java:721)
	at org.jfree.chart.plot.XYPlot.getDataRange(XYPlot.java:4048)
	at org.jfree.chart.axis.DateAxis.autoAdjustRange(DateAxis.java:1277)
	at org.jfree.chart.axis.DateAxis.configure(DateAxis.java:709)
	at org.jfree.chart.plot.XYPlot.configureDomainAxes(XYPlot.java:869)
	at org.jfree.chart.plot.XYPlot.datasetChanged(XYPlot.java:4078)
	at org.jfree.data.general.AbstractDataset.notifyListeners(AbstractDataset.java:185)
	at org.jfree.data.general.AbstractDataset.fireDatasetChanged(AbstractDataset.java:168)
	at com.inductiveautomation.factorypmi.application.components.chart.runtime.XYNumericDataSet.fireChange(XYNumericDataSet.java:63)
	at com.inductiveautomation.factorypmi.application.components.PMIChart.refreshChart(PMIChart.java:1032)
	at com.inductiveautomation.factorypmi.application.components.PMIChart.refreshChart(PMIChart.java:1003)
	at com.inductiveautomation.factorypmi.application.components.PMIChart.setPropertyValue(PMIChart.java:1259)
	at com.inductiveautomation.factorypmi.application.binding.AbstractPropertyAdapter.updateTarget(AbstractPropertyAdapter.java:297)
	at com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter.updateTarget(SQLPropertyAdapter.java:292)
	at com.inductiveautomation.factorypmi.application.binding.AbstractPropertyAdapter.updateValue(AbstractPropertyAdapter.java:261)
	at com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter.handleQueryReturnedValue(SQLPropertyAdapter.java:329)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$HandleReturnValueTask.run(QueryManager.java:477)
	at java.awt.event.InvocationEvent.dispatch(Unknown Source)
	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.awt.EventQueue.access$500(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.awt.EventQueue$3.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)

Ignition v7.9.9 (b2018081621)
Java: Oracle Corporation 1.8.0_371
7

The clue is in the first line.

Go to the Chart's properties and click on the dataset icon. Click on the Fechax column and read off the properties in the Column Name display below the data table. What is the 'Column Type'?