Problem with timeStamp

Ignition
,
1

Hello all,
I’m getting a “TypeError: Unable to convert row 22, column 2 to type class java.sql.Timestamp”
and I don’t know how to solve this.

I am trying to create a dataset with multiple row on the project script to populate a table on perspective.

Status = system.dataset.toDataSet(headers, data)
return Status

If I return the “data” and do the “system.dataset.toDataSet” from a button script all is fine but I want the function to return this dataset.

After some test I sorted out that on the error is caused by this row:
Dati: row 22 - 2021-03-10 11:46:54.074 - Wed Mar 10 14:29:57 CET 2021

all the other row are like this:
Dati: row 21 - 2021-03-10 11:46:53.144 - 2021-03-10 11:46:54.074

On the last row I force the second timestamp to system.date.now().

if I do the same on the script console all is fine

some ideas?

2

Can you post your actual code?
It looks like you’re creating/modifying a dataset with a column that’s a java class (Timestamp), but trying to insert some other type of value (a string?) into it instead.

3

Sure, my goal is to estract from a “system.tag.queryTagHistory” a dataset like this:

startTS, stopTS, value, duration

this is the script:

def calc(startTime,endTime,Paths):

	#endTime = system.date.now()
	#startTime = system.date.addDays(endTime, -12)

	hist=system.tag.queryTagHistory(paths, startDate=startTime, endDate=endTime, returnSize=-1, returnFormat='Wide')

	headers = ["Status", "Start", "End", "Duration"]
		
	data = []		
	
	system.util.getLogger("Dataset").info("Dati:"+ str(startTime)+ " - " +str(endTime)+ " - " +str(tagPaths) + " - ")
	#-----------------------------------------------------------------------------------------
	for row in range(hist.getRowCount()):
	
		eventStart = hist.getValueAt(row, 0)
		
		if row < (hist.getRowCount()-1):
			eventStop=hist.getValueAt(row+1, 0)
		else:
			eventStop=endTime

		system.util.getLogger("Dataset").info("Dati:"+ str(row)+ " - " +str(eventStart)+ " - " +str(type(eventStop)) + " - ")

		Duration = (eventStop.time - eventStart.time)/1000
		
		Value=hist.getValueAt(row, 1)
		
		data.append([Value, eventStart, eventStop, Duration])

	Stati = system.dataset.toDataSet(headers, data)
	return Stati

The way I’m calling is:

	#startTime=self.getSibling("DateTimePicker_Start").props.value
	#endTime=self.getSibling("DateTimePicker_End").props.value
	
	startTime=system.date.addYears(system.date.now(),-1)
	endTime=system.date.now()

		
	tagPaths=["[default]Test/Fase"]
	
	table = self.getSibling("Table_0")
	
	newData = SM_Report_try.calc(startTime,endTime,tagPaths)
	#table.props.data = newData
	self.view.custom.Dataset=newData

Thanks in advance,
also if you think there is a better way to do that I’m fully open

Fabio

4

Well, I don’t see anything obviously wrong with your code. Try logging the type() of your eventStart and eventStop values as you go through the loop? Something that’s not able to be coerced to a timestamp is slipping in there.

5

Yes the type looks different.
the one I’m getting from the query is java.sql.Timestamp
the one from system.date.now() is java.util.date

row 22 - 2021-03-10 11:46:54.074 - Wed Mar 10 14:29:57 CET 2021

The calculation works but I’m not able to insert into a dataset.
are any way to cast the type?

6

Yep,

import java.sql.Timestamp as Timestamp

now = system.date.now()
print now
print type(now)
print ' '
t = Timestamp(now.getTime())
print t
print type(t)

Output:

Thu Mar 11 13:01:01 EST 2021
<type 'java.util.Date'>
 
2021-03-11 13:01:01.618
<type 'java.sql.Timestamp'>
1 Like
7

Another option - use the DatasetBuilder class to explicitly state what type(s) to use for your columns. Timestamp is a subclass of java.util.Date, so Java can coerce in one direction but not the other.

2 Likes
8

Thanks,
I’m trying to follow this way but following the example give me no result.

import com.inductiveautomation.ignition.common.util.DatasetBuilder as DatasetBuilder

myDataset = DatasetBuilder.newBuilder()

myDataset.colNames(“SeriesName”, “FillColor”, “StrokeColor”, “StrokeWidth”)
myDataset.colTypes(String.class, Color.class, Color.class, Float.class)
myDataset.addRow(“Process Value”, TypeUtilities.toColor("#B8DAFF33"), Color.BLUE, 0.1)
myDataset.addRow(“Setpoint Value”, TypeUtilities.toColor("#FFFFFF44"), Color.DARK_GRAY, 0.2)
myDataset.build();

This give me: NameError: name ‘String’ is not defined

Also where I can find the class definition I can use?

Thanks in advance

9

I converted as jordan suggested
eventStop=Timestamp(endTime.getTime())

actually I’m facing a stange behaviour,now the code is executing.

If I look at the dataset when I’m using perspective i get all field formatted as java.sql.Timestamp

image
image1265×420 34.5 KB

If I use the script console I got the problem:

image
image1693×593 79.6 KB

This looks very strange

10

Then I’m not certain what causing your original issue, as all of those returned are java.util.Date, except for the one we coerced at the end.

11

Is it possibile to use a transaction group to achieve this type of setup?

[…some variable…,start_Timestamp,stop_Timestamp,Duration]

Thanks in advance

12

I'm running into the same problem that is documented above. I had one dataset that was returned from a gateway scope script, another one that was returned inside a Perspective property binding transform. I wanted to combine the two datasets, but the two types were not compatible.

My fix was to just have both datasets returned from a gateway script that way they were both the same type.