# Problem with timeStamp

**URL:** <https://forum.inductiveautomation.com/t/problem-with-timestamp/44309>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [March 10, 2021, 1:41pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309 "2021-03-10T13:41:11Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![fabio.gambarin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/fabio.gambarin/32/20449_2.png) [@fabio.gambarin](https://forum.inductiveautomation.com/u/fabio.gambarin)\
**Post date:** [March 10, 2021, 1:41pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/1 "2021-03-10T13:41:11Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 10, 2021, 4:03pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/2 "2021-03-10T16:03:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fabio.gambarin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/fabio.gambarin/32/20449_2.png) [@fabio.gambarin](https://forum.inductiveautomation.com/u/fabio.gambarin)\
**Post date:** [March 11, 2021, 1:34pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/3 "2021-03-11T13:34:52Z")

</div>

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

startTS, stopTS, value, duration

this is the script:

```auto
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:

```auto
	#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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 11, 2021, 5:36pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/4 "2021-03-11T17:36:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fabio.gambarin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/fabio.gambarin/32/20449_2.png) [@fabio.gambarin](https://forum.inductiveautomation.com/u/fabio.gambarin)\
**Post date:** [March 11, 2021, 5:54pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/5 "2021-03-11T17:54:39Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 11, 2021, 6:02pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/6 "2021-03-11T18:02:16Z")

</div>

Yep,

```auto
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:

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

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 11, 2021, 6:50pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/7 "2021-03-11T18:50:16Z")

</div>

Another option - use the [`DatasetBuilder`](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.1/com/inductiveautomation/ignition/common/util/DatasetBuilder.html) 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.

---

<div class="post-metadata">

**Author:** ![fabio.gambarin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/fabio.gambarin/32/20449_2.png) [@fabio.gambarin](https://forum.inductiveautomation.com/u/fabio.gambarin)\
**Post date:** [March 12, 2021, 10:36am UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/8 "2021-03-12T10:36:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![fabio.gambarin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/fabio.gambarin/32/20449_2.png) [@fabio.gambarin](https://forum.inductiveautomation.com/u/fabio.gambarin)\
**Post date:** [March 12, 2021, 12:52pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/9 "2021-03-12T12:52:11Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/5/45f7f8c15e7be6b5a2a6e6bc80ed8d5696abdcfa.png)

If I use the script console I got the problem:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb20491e7b0d08c82d25fcdb545b16163c74e149.png)

This looks very strange

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 12, 2021, 2:43pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/10 "2021-03-12T14:43:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fabio.gambarin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/fabio.gambarin/32/20449_2.png) [@fabio.gambarin](https://forum.inductiveautomation.com/u/fabio.gambarin)\
**Post date:** [March 25, 2021, 4:30pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/11 "2021-03-25T16:30:33Z")

</div>

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

[…some variable…,start\_Timestamp,stop\_Timestamp,Duration]

Thanks in advance

---

<div class="post-metadata">

**Author:** ![tmahaf](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tmahaf/32/23492_2.png) [@tmahaf](https://forum.inductiveautomation.com/u/tmahaf)\
**Post date:** [February 27, 2024, 6:55pm UTC](https://forum.inductiveautomation.com/t/problem-with-timestamp/44309/12 "2024-02-27T18:55:54Z")

</div>

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.