Convert Easy Chart .selectedXValue to Date

Hi Guys,

I have an Easy Chart that trends a 'parts produced' counter. I have given the user the ability to click somewhere in the chart (in Mark or X-Trace mode) then click a button that saves that timestamp to a text field component (startTimestamp). They then click a point on the chart further in time then click another button that saves that timestamp to another text field component (endTimestamp). I then use system.tag.queryTagHistory to extract a dataset of my counter in that time range, use a for loop to extract the min and max values and save max - min to a numeric text field component to show the user how many parts where produced in the selected range - all working very nicely.

I would now like to show parts produced per time in selected range. I think the best way to do this is to use system.date.*Between however if I do this using startTimestamp.text and endTimestamp.text I get an error:

TypeError: minutesBetween(): 1st arg can't be coerced to java.util.Date

Is there an easy way to convert the .selectedXValue string to a system.date object?

Got it working with the following script code:

startTimestamp = event.source.parent.getComponent('Text Field').text
endTimestamp = event.source.parent.getComponent('Text Field 1').text

startTimestampYear = int(startTimestamp[0:4])
startTimestampMonth = int(startTimestamp[5:7])
startTimestampDay = int(startTimestamp[8:10])
startTimestampHour = int(startTimestamp[11:13])
startTimestampMinute = int(startTimestamp[14:16])
startTimestampSecond = int(startTimestamp[17:19])

endTimestampYear = int(endTimestamp[0:4])
endTimestampMonth = int(endTimestamp[5:7])
endTimestampDay = int(endTimestamp[8:10])
endTimestampHour = int(endTimestamp[11:13])
endTimestampMinute = int(endTimestamp[14:16])
endTimestampSecond = int(endTimestamp[17:19])

startTimestampObject = system.date.getDate(startTimestampYear, startTimestampMonth, startTimestampDay)
startTimestampObject = system.date.setTime(startTimestampObject, startTimestampHour, startTimestampMinute, startTimestampSecond)

endTimestampObject = system.date.getDate(endTimestampYear, endTimestampMonth, endTimestampDay)
endTimestampObject = system.date.setTime(endTimestampObject, endTimestampHour, endTimestampMinute, endTimestampSecond)

event.source.parent.getComponent('Numeric Text Field 1').intValue = system.date.minutesBetween(startTimestampObject, endTimestampObject)

Would still like to know if this is the easiest way of doing this...

Look into the system.date.parse function.

Thanks PGriffith, looks promising but when I try some simple code like this:

selectedXValue = event.source.parent.parent.getComponent('Easy Chart').selectedXValue
event.source.parent.getComponent('Text Field').text = system.date.parse(selectedXValue, 'yyyy-mm-dd hh:mm:ss')

I get this error:

can't convert Sun Jan 13 07:20:23 PST 2019 to java.lang.String

The error is because you’re trying to set the text property of the text Field to a Date.

You should be using system.date.parse in the system.date.minutesBetween function.

startDateString = event.source.parent.getComponent('Text Field').text
startDate = system.date.parse(startDateString,'yyyy-mm-dd hh:mm:ss')
endDate = system.date.parse(selectedXValue,'yyyy-mm-dd hh:mm:ss')

mins = system.date.minutesBetween(startDate,endDate)
2 Likes

This might help:

In the NoteChart Module, both the EasyNoteChart and the classic NoteChart expose the X-Trace timestamp as an actual Date value in the traceTS property. And the traceTS property is writable, so you can script the setting and movement of the X-Trace.

1 Like

Hi Phil,

This morning I began trialing your NoteCharts module which I am loving so far!

I have given the user the ability to click anywhere in the chart which makes an ‘Insert Note’ button visible. Clicking this button executes a script which receives user text from an inputBox and puts it in a database with other info such as username, t_stamp of note, t_stamp added.

I have 2 minor issues:

  1. After I insert a not into the db, I currently I have to open another window and come back to this one to refresh the chart to see the note - is there a way to refresh the chart straight after it is inserted through scripting?
  2. When I first open the project then window, the chart is in X-Trace mode (chart.getMode() returns 4). If I go to another window and come back to this window it is all of a sudden in Zoom mode (chart.getMode() returns 0)… I have even tried running a script on the window’s internalFramActivated event handler to set the mode to 4 - does not work.

Hmmm. I haven't seen this. Are you editing a dataset in place, or are you assigning to the notes or altNotes properties? The latter is the correct way.

I'll play with this. Sounds odd. It is supposed to start in mode zero.

I have created a table in our db called notes which I plan to store all notes for all charts. I have bound the notes property to an SQL Query:

SELECT t_stamp,
userNote AS [text]
FROM notes

I am writing to this table directly like this:

userNote = system.gui.inputBox("Enter note:")
if userNote != None:
easyNoteChart = event.source.parent.parent.getComponent('Easy Note Chart')
t_stamp = easyNoteChart.traceTS
userName = system.security.getUsername()
databaseConnection = "NewConnection_Sandbox"
system.db.runPrepUpdate("INSERT INTO notes (t_stamp, userNote, userName) VALUES (?,?,?)", [t_stamp, userNote, userName], databaseConnection)
event.source.visible = 0

system.nav.closeParentWindow(event)
window = system.nav.openWindow('Main Windows/Production Data/Trends Dev')
system.nav.centerWindow(window)

Those last three lines of code are my quick fix to update the chart which is working fine.

Unsure how else it can be done...

Ok. You just need to use system.db.refresh() to tell your binding to run its query again. Replace the last four lines with this:

system.db.refresh(event.source, 'notes')
1 Like

Chart updating after note inserted into db - Thanks Phil!

For completeness I am trying to specify a where clause for the notes property SQL Query binding. I started with this which works well when in Historical mode:

SELECT t_stamp,
userNote AS [text]
FROM notes
WHERE t_stamp >= '{Root Container.Easy Note Chart.startDate}' AND
t_stamp <= '{Root Container.Easy Note Chart.endDate}'

Unfortunately, this does not work when in Realtime mode.

Trying something like this but getting errors:

SELECT t_stamp,
userNote AS [text]
FROM notes
WHERE t_stamp >= '{Root Container.Easy Note Chart.startDate}' AND
CASE '{Root Container.Easy Note Chart.chartMode}'
WHEN 1 THEN t_stamp <= '{Root Container.Easy Note Chart.endDate}'
WHEN 2 THEN t_stamp <= CURRENT_TIMESTAMP
END

Figured it out:

SELECT t_stamp,
userNote AS [text]
FROM notes
WHERE t_stamp >= '{Root Container.Easy Note Chart.startDate}' AND
t_stamp <= CASE {Root Container.Easy Note Chart.chartMode}
WHEN 0 --Manual mode
THEN '{Root Container.Easy Note Chart.endDate}'
WHEN 1 --Historical mode
THEN '{Root Container.Easy Note Chart.endDate}'
WHEN 2 --Realtime mode
THEN CURRENT_TIMESTAMP
END

Try not to do such pre-query value selections in the SQL – it just makes it run slower. Use custom properties with suitable expressions to select the correct bounding dates. Also consider using a Named Query with parameters for the dates – that’ll let you depend on JDBC to pass them correctly, even down to milliseconds, regardless what database type you use.

I'm not quite sure what you mean, none of the chart components can have custom properties. Can you provide an example?

Thanks, yes, I was planning to convert to a named query once I had it working how I want. When I did the core training late last year the instructor said that we should only use named queries for various reasons.

Use custom properties on the chart's container where necessary.

Some instructor needs to be chastised. Named queries should be used everywhere you can. In particular, their ability to pass parameters is most helpful with date/time datatypes. String substitution on those is a crapshoot and generally screws up vital time zone information.

My apologies for the ambiguity of my statement. I meant that he told us to use named queries wherever possible.

Phil, would love an onDoubleClick extension function for the charts - this would allow me to get rid of the ‘Insert Note’ button (which I am finding difficult to get how I want by changing it’s viability). Just something for you to consider!

Another nice to have would be to expose the other chart mode (Zoom, Pan, Mark, X-Trace) in the property editor.