Query stopped working do to date time format syntax after upgrading from 7.9.8 to 7.9.11

Ignition
1

I have a simple query that worked previously, but after upgrading from 7.9.8 to 7.9.11 I am getting an Oracle error ORA-01830. Here is the query in question:
SELECT "t_stamp", "assm_parts" FROM DBH where "t_stamp" BETWEEN TO_DATE('{Root Container.Date Range.startDate}', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('{Root Container.Date Range.endDate}', 'YYYY-MM-DD HH24:MI:SS')

Oracle date syntax is a pain, but I didn’t think updating Ignition would have any effect. And it worked previously. Here it the full error:

SQLQuery(query=SELECT "t_stamp", "assm_parts"
FROM DBH
where "t_stamp" BETWEEN
			TO_DATE('2019-06-08 02:00:00.000', 'YYYY-MM-DD HH24:MI:SS')
			AND TO_DATE('2019-06-09 02:00:00.000', 'YYYY-MM-DD HH24:MI:SS'), database=ignition_scada_test)@0ms
On: metrics.Root Container.Chart.Data
	caused by GatewayException: ORA-01830: date format picture ends before converting entire input string

	caused by SQLDataException: ORA-01830: date format picture ends before converting entire input string
2

Oh it looks like perhaps going from 7.9.8 added millisecond precision to my startDate and endDate properties. Does anyone know a good way to change the precision on the startDate and endDate from the Date Range object?

3

You could create a hidden label or text box controls and bind them to an expression of
dateFormat({Root Container.Date Range.startDate},'yyyy-MM-dd HH:mm:ss')
Then pass those values to your query?
I don’t deal with Oracle dates so I am not sure if that will work or not.

4

yeah I am trying that, for whatever reason java is passing a fractional second and I can’t get the syntax right on the Oracle side to accept the fractional second

5

Ok I finally got it to work. In case anyone running Oracle DB runs in to this, you must convert TO_TIMESTAMP in your query and not TO_DATE, because TO_DATE does not support fractional seconds.

Working query (I have gone back to just calling the date range property, not a custom property):

SELECT "t_stamp", "assm_parts" FROM DBH where "t_stamp" BETWEEN TO_TIMESTAMP('{Root Container.Date Range.startDate}','YYYY-MM-DD HH24:MI:SS.FF3') AND TO_TIMESTAMP('{Root Container.endDate}','YYYY-MM-DD HH24:MI:SS.FF3')

Can anyone confirm that between 7.9.8 and 7.9.11 the precision changed on the Java side?

1 Like
6

You should convert to named queries and let JDBC handle these conversions (via named parameters) for you. Milliseconds on Date() objects is handled appropriately in pretty much all cases.

2 Likes
7

Thank you for this. We had the exact situation and to_timestamp helped!