Parsing date from perspective table

Ignition
1

Hi,

I have a perspective table which is populated by a named query. The results of the query are then ran though a transform script, the output of which is a JSON dataset.

I have an "onRowClick" event script in which I would like to take some of the values and execute another query.

An example of the event created when clicking a row is below:

{"row":5,"rowIndex":5,"value":{"event_id":84,"NAME":"***** *******","DATE":"Wed Feb 21 2024 00:00:00 GMT+0000 (Greenwich Mean Time)","HOURS":"08:00-16:00","REQUESTER COMMENT":"","APPROVED?":2,"APPROVER COMMENT":""}}

The date is returned as a string and I am struggling to parse it. I have tried some different string formats but none are working:

system.date.parse(eventDate,"ddd MMM DD YYYY HH:mm:ss z", "en-GB")

system.date.parse(eventDate,"E MMM dd yyyy HH:mm:ss z", "en-GB")

Can anyone point me in the right direction with this?

Thanks,
Leigh

2

Don't manipulate dates as strings. Keep them as dates, and set your component to render them correctly. Then you don't have to parse them. (You should only ever parse user-supplied date strings.)

3

Thanks for your reply.

If I have changed dates to strings at any point it has been unintentional and I don't know where that would have happened.

The named query returns a date from the database. The transform script is only meant to add styling to the table. I haven't explicitly done anything to alter values.

def transform(self, value, quality, timestamp):
	output_json = []
	    
	style_red = {"backgroundColor": "LightCoral"}
	style_green = {"backgroundColor": "LightGreen"}
	style_yellow = {"backgroundColor": "PaleGoldenRod"}
	
	for row in range(value.getRowCount()):
		row_object = {}
					
		for col in value.getColumnNames():    
			cell_object = {}
			cell_style = {}
			# Add a key/value pair for the cell
			cell_object['value']=value.getValueAt(row, col)
			
			# Select the style
			if col == 'APPROVED?':
				status = value.getValueAt(row, col)
				if status == 0:
					cell_style = style_red
				elif status == 1:
					cell_style = style_green
				elif status == 2:
					cell_style = style_yellow
			
			cell_object['style']=cell_style
			
			#Add the cell object to the row object
			row_object[col]=cell_object
		
		#Add the row_object to the output					
		output_json.append(row_object)	
		
	return output_json

The dates in the table format as expected.
image

It is only when I work with the data from the onRowClick event that the date seems to change to a string. Should I be doing a system.util.jsonDecode first before working with the data?

4

I have no idea why the date would become a string, but did you configure the columns property on the table ? Maybe, somehow, this has an effect on the datatype of the variable you get from your onRowClick script ?
Can we see that script, by the way ?

Also, if you're gonna use json, you might as well change the query binding's return format to json. Should make the transform simpler.

edit: I just tried, with mssql and a datetime, I get a long with the timestamp in the onRowClick event.