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
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.)
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](https://global.discourse-cdn.com/inductiveautomation/original/3X/5/6/56bdbbfdf66464fe3544303da6b0ce4b69bae451.png)
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?
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.