Perspective: Ad Hoc Trend Version 1.0.5 - Start/End Datetime Not Loading into Power Chart Correctly

mudbay · December 3, 2020, 6:55pm

Really like the direction that the Ad Hoc Trend Version 1.0.5 is heading. Nice work.

I have a question (perhaps a bug) that I could use some help on. What I have noticed is that when using the Save function to a JSON file with a Start and End history date, and then reloading the JSON file into the Ad Hoc trend, the trend itself does not seem to display the Start/End history range in the trend.

I have checked the JSON file to see if the Start and End date/times are correct and they seem to be OK.

dateFormat “YYYY/MM/DD”
endDate "Dec 2, 2020, 11:59:00 PM"
measureOfTime “minutes”
mode “historical”
pointCount 300
rangeEndDate “Dec 2, 2020, 10:39:32 PM”
rangeSelectorPen “histprov:MSSQLExpress:/drv:ignition-vdevignition01:sample_tags:/tag:random/randomboolean2”
rangeStartDate “Dec 2, 2020, 4:08:05 PM”
refreshRate 1000
responsiveDesignWidth 750
startDate "Dec 2, 2020, 2:35:00 AM"
tagBrowserStartPath " "
timeFormat “h:mm A”
unitOfTime

Is there something that I am missing with the implementation that I need to adjust? (i.e. is the dateFormat property a potential issue (this is the default format of the object in JSON file which I have not changed). This is auto populated with the Date/time picker built into the Perspective Power Chart.

Thanks in advance for your help.

mudbay · December 7, 2020, 12:50am

More on this thread:

Determined that the JSON is loaded into the PowerChart.config PROPS and the Start and End dates are correct. When the trend loads, it is in Historical mode, but the PowerChart does NOT load in the Start and End dates. (Notice date range on Trend is Dec 6. when it should be Dec. 2)

How I instrumented code to confirm PowerChart.config PROPS:

Does anyone know why the chart will not load with the correct Start/End datatime?

epsmitas · December 7, 2020, 3:19am

Wondering if the date is the wrong format ? I recall seeing that it should be a numeric format ( powerchart property ). Could it be that when it was written to the database table the date was converted ? Recall seeing in the write function that the json was turned to a string before being written. Would that change the format ?

epsmitas · December 7, 2020, 4:08am

Should be YYYY/MM/DD

mudbay · December 7, 2020, 5:11am

Thanks epsmitas. I think you are on to something. I had some suspicion around date/time formats. I posted up above. What I have noticed (and I don’t know how to fix it yet) is the following:

When you use the Power Chart DateTimePicker, the time that it populates into the Props.config.startDate and endDate are in UTC or Zulu time (at least as displayed in the Perspective Label component)

When I load a saved Trend the times are different format (JSON file):

This format, obviously, does not “sit well” with the Power Chart and is rejected (I suspect), so the chart does not load to the correct date/time.

Note: I am using Chrome browser

mudbay · December 7, 2020, 5:23pm

Not sure how to contact Matthew Raybourn to ask him some questions about this Ad Hoc trend. I can see that the JSON date/time format is somehow not being stored properly and when it is retrieved, consequentially causes the Start End DateTimes to be in the wrong format. It may be in the method call “pyToGson” in the getConfigjson Script. I CAN definitely recreate the issue and see the Datetime issue. Sure would like to fix this as I don’t know how people are actually storing and retrieving trends properly with the too.

epsmitas · December 8, 2020, 9:21pm

pyToGson is converting the date from 2020-12-8 16:14:27 to Dec 8, 2020, 4:14:27 PM

from com.inductiveautomation.ignition.common import TypeUtilities
return str(TypeUtilities.pyToGson(config))

config = PyDataset form all Powerchart props put togeather.

Now just to convert it back by script…

mudbay · December 9, 2020, 7:49pm

I may be showing my inexperience with the product, but I have tried many approaches to solving this problem. I have the script in place to convert the DateTime into the correct format that the Power Chart will accept, however, there seems to be a fundamental issue with the system.util.jsonDecode() method used in the object when it comes to the datetime properties being returned by the LoadFromFile Custom Method. (in compatibilties between pyToGson and jsonDecode). Below I just tried to print out what is returned in the jsonDecode for the startDate property.

if configJson:
config = system.util.jsonDecode(configJson)
powerChart = self.view.getChild(‘root/PowerChart’)

	if includePlots:
		if 'config' in config:
			powerChart.props.config = config['config']
			# Just try and print the actual string in the startDate property -
			system.perspective.print("OLD Datetime: ", powerChart.props.config.startDate)

This “print” statement throws the following exception (shortened version):
Caused by: org.python.core.PyException: Traceback (most recent call last): File “”, line 7, in runAction File “”, line 13, in loadFromFile java.lang.IllegalArgumentException: java.lang.IllegalArgumentException: Invalid UUID string: Dec 2, 2020, 2:35:00 AM <— You can see that the data is being returned, but somehow it is in an unacceptable format for the Print statement

At the moment, I am exhausted from trying so many different approaches to rectify this problem that normally I would not even be remotely contemplating with other Historians as saving Trends is “out of the box” with all other Historians I have used (i.e. Wonderware, OSI, AspenTech etc.). I would like to “lick” this problem for the community as this is a fundamental tool to any project (in my mind) with the product, but at the moment I feel like I am playing a losing battle.

PGriffith · December 9, 2020, 7:53pm

Your exception there isn’t related to json encoding or decoding - system.perspective.print just only expects one ‘message’ argument. Concatenate your strings into a single argument.

mudbay · December 9, 2020, 8:00pm

I am sorry, but I am a little (or a lot) confused. The print statement is: system.perspective.print("OLD Datetime: ", powerChart.props.config.startDate). When you say “Concatenate”, what am I concatenating?

Is the powerChart.props.config.startDate not a single String property of the JSON object? When I look at the JSON file it looks to be just a string.

PGriffith · December 9, 2020, 8:58pm

Ignoring the JSON (it’s not relevant to the error you’re getting) -
system.perspective.print does not have the same signature/contract as Python’s builtin print function/statement:
https://docs.inductiveautomation.com/display/DOC81/system.perspective.print
It only accepts one argument, which it will coerce to a string, as the message. By calling it as:
system.perspective.print("OLD Datetime: ", powerChart.props.config.startDate)
You’re providing "OLD Datetime: " as the message argument, and the startDate prop as the sessionId parameter - that is where the ‘cannot coerce Date to UUID’ error is coming from.
What I’m suggesting instead is to format/concatenate the message argument:
system.perspective.print("OLD Datetime: %s" % powerChart.props.config.startDate)
So that you’re only passing one argument, the message.

mudbay · December 9, 2020, 11:51pm

Ahhh…thanks…Rookie mistakes…OK I understand…thanks.

Having that issue out of the way my on going challenge is the datetime issue.

From the Designer Console:

startDate = “Dec 2, 2020, 2:35:00 AM” #<-- this is the JSON return string that is coming from the file
print(startDate)
#create datetime object, so I can manipulate the date time format
date_time_obj = datetime.datetime.strptime(startDate, ‘%b %d, %Y, %I:%M:%S %p’)
#change date time to acceptable format for Power Chart
print(date_time_obj.strftime(’%a %b %d %I:%M:%S %p %Y’))

Output looks good:

Dec 2, 2020, 2:35:00 AM
Wed Dec 02 02:35:00 AM 2020 <-- this is the “good” time format

When implemented within the Event Script

import datetime # CJG added to deal with datetime issues
if configJson:
config = system.util.jsonDecode(configJson)
powerChart = self.view.getChild(‘root/PowerChart’)

	if includePlots:
		if 'config' in config:
			powerChart.props.config = config['config']
			# Create datetime object in format returned by jsonDecode() and convert to format that is acceptable by Power Chart Obj.
			startDate = (powerChart.props.config.startDate.strip()).encode() #remove leading and trailing spaces on string. Just in case
			startDateDisplay = "OLD Datetime: " + startDate
			system.perspective.print(startDateDisplay)
			#output from print above is OLD Datetime: Dec 2, 2020, 2:35:00 AM
			startDateObj = datetime.datetime.strptime(startDate, '%b %d, %Y, %I:%M:%S %p')
			system.perspective.print("New Start Date: %s" % startDateObj.strftime('%a %b %d %I:%M:%S %p %Y'))

Output of this is:

OLD Datetime: Dec 2, 2020, 2:35:00 AM
Then exception is thrown…match format %r" % ValueError: time data ‘Dec 2, 2020, 2:35:00 AM’ does not match format ‘%b %d, %Y, %I:%M:%S %p’

Any thoughts on the exception reason when it works in the console?

pturmel · December 9, 2020, 11:58pm

Consider using java.util.Date and Calendar. Python datetime in jython is a hot mess.

mudbay · December 10, 2020, 12:08am

Thanks “ptumel” I will look into this. Having said this, the core root of my problem is that the jsonDecode method used in the Ad Hoc Trend package to load the JSON file stored with the Power Chart properties, is changing the date time format (to an unacceptable format) when it reads the JSON file created through a pyToGson method (pyToGson is storing datetime correctly). Do you have any ideas to potentially change the way data is restored from the file so the date/time is not changed when the JSON file is loaded? This way I would not have to go to all the efforts of correcting this error in the method that I have been working on above?

PGriffith · December 10, 2020, 12:29am

There’s a corresponding gsonToPy function available in TypeUtilities - you could try that, instead of system.util.jsonDecode.

mudbay · December 10, 2020, 12:36am

I think the original designer had an issue with this method (I know this would seem like an obvious method to use). I will give this a try (again), but I think it threw an exception on me if I recall…

So many angles that I have attached the problem at that I have somewhat lost track.

mudbay · December 10, 2020, 4:00am

Ok. So implemented the gsonToPy method to see what would happen.

if configJson:
#config = system.util.jsonDecode(configJson) #original code in package
config = TypeUtilities.gsonToPy(configJson)
powerChart = self.view.getChild('root/PowerChart')
....

Result:
gsonToPy(): 1st arg can't be coerced to com.inductiveautomation.ignition.common.gson.JsonElement

I think this is what the original designer discovered and then switched to the jsonDecode method. I think I am at a "dead end". I have exhausted the bag of tools available that I know of at this point. I am hoping someone better than me can resolve the issue as the Ad Hoc Chart has a lot of great promise and the work done so far is commendable.

mudbay · December 10, 2020, 5:32am

Gave this a try too…but same issue (per suggestion from pturmel)

from java.text import SimpleDateFormat # add this to see if it can fix date issue

if configJson:
	config = system.util.jsonDecode(configJson) #original code in package
	powerChart = self.view.getChild('root/PowerChart')
	
	if includePlots: :tired_face:
		if 'config' in config:
			powerChart.props.config = config['config']
			startDateObj = SimpleDateFormat("MMM dd, YYYY, kk:mm:ss aa").parse(powerChart.props.config.startDate)
			#Dec 2, 2020, 2:35:00 AM
			system.perspective.print("New Start Date: %s" % startDateObj.toString())

Exception thrown:
java.text.ParseException: java.text.ParseException: Unparseable date: "Dec 2, 2020, 2:35:00 AM"
at org.python.core.Py.JavaError(Py.java:552) More detail on this error:Error running action ‘component.onActionPerformed’ on AdHocTrends/AdHocTrends@C/root/MainTopControls/HeaderLarge/LoadFromFile/Load: Traceback (most recent call last): File “function:runAction”, line 7, in runAction File “”, line 17, in loadFromFile at java.base/java.text.DateFormat.parse(Unknown Source) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.text.ParseException: java.text.ParseException: Unparseable date: “Dec 2, 2020, 2:35:00 AM”

Tried this in the Console with same datetime:

from java.text import DateFormat,SimpleDateFormat

dparse = SimpleDateFormat(“MMM dd, YYYY, kk:mm:ss aa”).parse(“Dec 2, 2020, 2:35:00 AM”)
print(dparse.toString().encode())

Output:

Sun Dec 29 02:35:00 PST 2019 <–Works in console with same string for date that threw exception above in the Perspective script.

PGriffith · December 10, 2020, 4:57pm

Oof - it's because (for the sake of friendliness) we automatically coerce JsonElement classes into a slightly different form from Jython's perspective. Try this (untested, but should be close to a working solution):

def gsonToPy(json):
	if json is None or json.isJsonNull():
		return None
	else if json.isJsonObject():
		return dict(json.items())
	else if json.isJsonArray():
		return list(json)
	else if json.isBoolean():
		return bool(json.getAsBoolean())
	else if json.isString():
		return str(json.getAsString())
	else:
		return json.getAsNumber()

mudbay · December 10, 2020, 10:20pm

I hope I didn’t miss lead you but just to be clear…

The problem resides in the fact that the core pyToGson method changes the date format to an incompatible format to what Power Chart expects. This is what happens in simplified form:

Before pyToGson method call, the date has format example: Thu Dec 03 05:55:00 PST 2020 <-- Good format.

2.) After pyToGson method is called, the date has the following format: Dec 3, 2020, 5:55:00 AM <–Bad format.

newPyToGson method?

So the fundamental problem is with the way pyToGson stores the date. We either need a newPyToGson method that does no “coerce” the date or some method upon the loading where we can reformat these “bad dates” to “good dates”. PGriffith: is there an equivalent method that you have stated above for the “gsonToPy” method that could be used to create a newPyToGson that does not coerce dates?

I have a simple script “proof of concept” to convert “bad date” to “good date” upon loading that works in the Console code that I used to test is as follows:

Loading files into Ignition from Console

import system.file.openFiles, system.file.readFileAsString, system.util.jsonDecode
from java.text import SimpleDateFormat
filePath = system.file.openFile(‘JSON’, ‘<your path goes here>’)
print(filePath)
fileAsString = system.file.readFileAsString(filePath)
print (fileAsString)

#Convert String to JSON
fileAsJson = system.util.jsonDecode(fileAsString)
print("*** File as JSON: *" )
print(fileAsJson)
print(" JSON property startDate ***")
startDate = fileAsJson[‘config’][‘startDate’]
print(startDate)
startDateObj = SimpleDateFormat(“MMM dd, yyyy, kk:mm:ss aa”).parse(startDate)
print(startDateObj) #printing object
print(startDateObj.toString()) #printing just date string

Perspective Custom Method (Doesn’t work)

from java.text import SimpleDateFormat #CJG added

if configJson:
	config = system.util.jsonDecode(configJson) #original code in package
	powerChart = self.view.getChild('root/PowerChart')
	
	if includePlots:
		if 'config' in config:
			powerChart.props.config = config['config']
			#CJG added to see if we can change date format
			system.perspective.print("****loadFromFile startDate: %s" % powerChart.props.config.startDate)
			startDateObj = SimpleDateFormat("MMM dd, YYYY, kk:mm:ss aa").parse(powerChart.props.config.startDate)
			system.perspective.print(startDateObj.toString())  #printing just date string